AnsweredAssumed Answered

NSQL Query Help

Question asked by Tammi Champion on May 3, 2010
Latest reply on May 24, 2010 by Tammi
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. Any help I can get would be great. I am trying to add the OBS Filter in my portlet  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.uniqu   e_name:ProjectID@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.descr iption:Description@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prsta rt:ProjectStart@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prfin ish:ProjectFinish@ ,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_s mf:SMF@,
@Select:Dim_Prop:User_Def:Implied:Project:lps3.nam e:ProjectPhase@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_p rj_phase:Phaseinternalid@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_o verall_res_bdgt:OverallResourceBudget@,
@Select:Dim_Prop:User_Def:Implied:Project:dept.sho rtdesc:Department@,
@Select:Dim_Prop:User_Def:Implied:Project:(cmn.las t_name ||', '||cmn.first_name):ProjectManger@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prpri ority:Priority@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.statu s_indicator:StatusIndicator@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.statu s_comment:StatusComment@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_n ext_go_live:GoLive@,
@Select:Dim_Prop:User_Def:Implied:Project:lps.name :Customer@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.actu al_hours:ACT@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.etc_ hours:ETC@,
@select:dim_prop:user_def:implied:Project:cop_calc finishtime_fct(pp.prfinish):finish_date@,
@select:dim_prop:user_def:implied:Project:cop_calc finishtime_fct(pp.prbasefinish):base_finish_date @,
@select:metric:user_def:implied:(cop_calc_finish_f ct(pp.prBaseFinish) - cop_calc_finish_fct(pp.prFinish)):days_late@,
@select:metric:user_def:implied:(odf.eh_overall_re s_bdgt)-(nbi.actual_hours) + (nbi.etc_hours):Total@,
@select:metric:user_def:implied:(odf.eh_overall_re s_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@[left][left]

Outcomes