AnsweredAssumed Answered

Assistance with an nSQL query

Question asked by picis on Feb 3, 2009
Latest reply on Feb 11, 2009 by picis
I could really use some assistance with an nSQL query I've been working on.  I created the SQL query and it runs fine in SQL, but I'm having a hard time getting it to compile in Clarity correctly. Here's what I'm looking for.  We have a task on all our projects, I'm trying to create a query (then portlet) to display the % that task is of the total remaining ETC for a project.  Here's the SQL query: select a.id as proj_id, a.name as proj_name, a.code as proj_code, h.contract_no as cont_num, e.id as pmid, e.full_name as pm_name, a.is_active as active, a.chargecodeid, i.prname as chrg_code, h.proj_type as proj_typeid, j.name as proj_type, a.labor_etcsum/3600 as proj_etc, a.labor_actsum/3600 as proj_act, a.labor_eacsum/3600 as proj_ttl_effrt, sum(z.prestsum/3600) as conting_task_etc, CASE WHEN (sum(z.prestsum) > 0) and ((a.labor_etcsum - sum(z.prestsum)) WHEN (sum(z.prestsum) = 0) and ((a.labor_etcsum - sum(z.prestsum)) = 0) THEN 0 ELSE (((sum(z.prestsum/3600))/(a.labor_etcsum/3600 - sum(z.prestsum/3600)) * 100)) END as conting_prcnt from prassignment z -- Assignment Details JOIN prtask c ON z.prtaskid = c.prid -- Task Details JOIN inv_investments a ON c.prprojectid = a.id  -- Project Details JOIN srm_resources b ON z.prresourceid = b.id -- Assignment Resource Details JOIN srm_resources e ON a.manager_id = e.user_id  -- Project Mgr Details JOIN odf_ca_project h ON a.id = h.id -- Additional Project Details JOIN prchargecode i ON a.chargecodeid = i.prid -- Charge Code Details LEFT OUTER JOIN cmn_lookups_v j ON h.proj_type = j.id  -- Project Type lookup where c.prname like '%contingency%' -- Task name contains contingency and b.id = '5001026' -- 'PICIS – Contingency' resource and (j.language_code = 'en' OR j.language_code IS NULL) and c.prismilestone = '0' -- Is not a milestone task group by a.id, -- project id a.name, -- project name a.code,  -- project code h.contract_no, -- contract # e.id, -- pm id e.full_name, -- pm name a.is_active, -- is project active a.chargecodeid, -- charge code id i.prname, -- charge code name h.proj_type, -- project type id j.name, -- project type name a.labor_etcsum, -- project etc a.labor_actsum, -- project actuals a.labor_eacsum -- project total effort  I've tried several different itterations with no success.  I'll include one of the nSQL versions below as a reference; I've commented out a couple of the fields to simply get it to compile, I don't even know how to begin to form the 'conting_prcnt' field. SELECT @SELECT:DIM:USER_DEF:IMPLIED:project:main.proj_id:proj_id@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_name:proj_name@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_code:proj_code@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.cont_num:cont_num@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.pm_name:pm_name@,  @SELECT:DIM_PROP:USER_DEF:BOOLEAN:project:main.active:active@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.chargecodeid:chargecodeid@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.chrg_code:chrg_code@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_typeid:proj_typeid@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_type:proj_type@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_etc:proj_etc@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_act:proj_act@,  @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:main.proj_ttl_effrt:proj_ttl_effrt@, @SELECT:METRIC:USER_DEF:IMPLIED:convert(decimal(18,2),sum(main.conting_task_etc/3600)):Cont_ETC:agg@   from ( select a.id as proj_id, a.name as proj_name, a.code as proj_code, h.contract_no as cont_num, e.id as pmid, e.full_name as pm_name, a.is_active as active, a.chargecodeid, i.prname as chrg_code, h.proj_type as proj_typeid, j.name as proj_type, a.labor_etcsum/3600 as proj_etc, a.labor_actsum/3600 as proj_act, a.labor_eacsum/3600 as proj_ttl_effrt, z.prestsum as conting_task_etc /*, sum(z.prestsum/3600) as conting_task_etc , CASE WHEN (sum(z.prestsum) > 0) and ((a.labor_etcsum - sum(z.prestsum)) WHEN (sum(z.prestsum) = 0) and ((a.labor_etcsum - sum(z.prestsum)) = 0) THEN 0 ELSE (((sum(z.prestsum/3600))/(a.labor_etcsum/3600 - sum(z.prestsum/3600)) * 100)) END as conting_prcnt*/ from prassignment z -- Assignment Details JOIN prtask c ON z.prtaskid = c.prid -- Task Details JOIN inv_investments a ON c.prprojectid = a.id  -- Project Details JOIN srm_resources b ON z.prresourceid = b.id -- Assignment Resource Details JOIN srm_resources e ON a.manager_id = e.user_id  -- Project Mgr Details JOIN odf_ca_project h ON a.id = h.id -- Additional Project Details JOIN prchargecode i ON a.chargecodeid = i.prid -- Charge Code Details LEFT OUTER JOIN cmn_lookups_v j ON h.proj_type = j.id  -- Project Type lookup where c.prname like '%contingency%' -- Task name contains contingency and b.id = '5001026' -- 'PICIS – Contingency' resource and (j.language_code = 'en' OR j.language_code IS NULL) and c.prismilestone = '0' -- Is not a milestone task group by a.id, -- project id a.name, -- project name a.code,  -- project code h.contract_no, -- contract # e.id, -- pm id e.full_name, -- pm name a.is_active, -- is project active a.chargecodeid, -- charge code id i.prname, -- charge code name h.proj_type, -- project type id j.name, -- project type name a.labor_etcsum, -- project etc a.labor_actsum, -- project actuals a.labor_eacsum -- project total effort) main where @FILTER@ group by main.proj_id, main.proj_name, main.proj_code, main.cont_num, main.pmid, main.pm_name, main.active, main.chargecodeid, main.chrg_code, main.proj_typeid, main.proj_type, main.proj_etc, main.proj_act, main.proj_ttl_effrt HAVING @HAVING_FILTER@ Any assistance/guidance would be greatly appreciated!Thanks in advance,  - Mark

Outcomes