AnsweredAssumed Answered

Help with Query for OBS

Question asked by Tammi Champion on May 3, 2010
Latest reply on May 7, 2010 by Chris_Hackett
Can someone please help me with this query, I am getting back duplicate dimension data, I am racking my brain trying to figure this out.  SELECT    
@Select:Dim:User_def:Implied:Project:sp.id:id@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.name:ProjectName@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.unique_name:ProjectID@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.description:Description@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prstart:ProjectStart@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prfinish:ProjectFinish@ ,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_smf:SMF@,
@Select:Dim_Prop:User_Def:Implied:Project:lps3.name:ProjectPhase@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_prj_phase:Phaseinternalid@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_overall_res_bdgt:OverallResourceBudget@,
@Select:Dim_Prop:User_Def:Implied:Project:dept.shortdesc:Department@,
@Select:Dim_Prop:User_Def:Implied:Project:(cmn.last_name ||', '||cmn.first_name):ProjectManger@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prpriority:Priority@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.status_indicator:StatusIndicator@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.status_comment:StatusComment@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_next_go_live:GoLive@,
@Select:Dim_Prop:User_Def:Implied:Project:lps.name:Customer@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.actual_hours:ACT@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.etc_hours:ETC@,
@select:dim_prop:user_def:implied:Project:cop_calc_finish_time_fct(pp.prfinish):finish_date@,
@select:dim_prop:user_def:implied:Project:cop_calc_finish_time_fct(pp.prbasefinish):base_finish_date@,
@select:metric:user_def:implied:(cop_calc_finish_fct(pp.prBaseFinish) - cop_calc_finish_fct(pp.prFinish)):days_late@,
@select:metric:user_def:implied:(odf.eh_overall_res_bdgt)-(nbi.actual_hours) + (nbi.etc_hours):Total@,
@select:metric:user_def:implied:(odf.eh_overall_res_bdgt)-((nbi.actual_hours) + (nbi.etc_hours)):Variance@,
@select:metric:user_def:implied:(((odf.eh_overall_res_bdgt)-((nbi.actual_hours) + (nbi.etc_hours)))/(odf.eh_overall_res_bdgt)) * 100:Variance2@,
@select:metric:user_def:implied:(inv_projects.RCF_FLEXIBILITY * 1.000000 + inv_projects.RCF_FUNDING * 1.000000 + inv_projects.RCF_HUMAN_INTERFACE * 1.000000 + inv_projects.RCF_IMPLEMENTATION * 1.000000 + inv_projects.RCF_INTERDEPENDENCY * 1.000000 + inv_projects.RCF_OBJECTIVES * 1.000000 + inv_projects.RCF_ORG_CULTURE * 1.000000 + inv_projects.RCF_RESOURCE_AVAIL * 1.000000 + inv_projects.RCF_SPONSORSHIP * 1.000000 + inv_projects.RCF_SUPPORTABILITY * 1.000000 + inv_projects.RCF_TECHNICAL * 1.000000) / (1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000 + 1.000000):risk@
FROM        
srm_projects sp,
odf_ca_project odf,    
prj_projects pp ,
cmn_sec_users cmn ,
departments dept,
prj_obs_associations obs,
cmn_lookups_v lps,
cmn_lookups_v lps3,
nbi_project_current_facts nbi,
inv_projects inv_projects,
prj_obs_units_flat flat
where
sp.id=odf.id (+)
and pp.prid=sp.id
and obs.unit_id=flat.unit_id (+)
and nbi.project_id=sp.id  
and cmn.id=pp.manager_id
and obs.record_id=sp.id (+)
and obs.unit_id=dept.obs_unit_id
and sp.id=inv_projects.prid
and obs.table_name ='SRM_PROJECTS'
and lps.lookup_code (+)= odf.EH_CUSTOMER
and lps.lookup_Type (+)='EH_CUSTOMER'
and lps.language_code (+)= 'en'
and lps3.lookup_code (+) = odf.eh_prj_phase
and lps3.lookup_Type (+)='EH_PRJ_PHASE'
and lps3.language_code (+)= 'en'
and lps3.is_active(+)=1
and   sp.is_active=1
and odf.eh_non_prj=0
and sp.is_template=0
and sp.is_program=0
AND ((flat.branch_unit_id IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:flat.branch_unit_id:OBS@))
AND           @FILTER@

Outcomes