AnsweredAssumed Answered

Portlet with duplicate dimensional data

Question asked by mtuscic on Oct 20, 2016

Portlet is showing error: NPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete

Query is:

SELECT DISTINCT
@SELECT:DIM:USER_DEF:IMPLIED:mngprojects:inv.ID:inv_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.NAME:inv_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:INV.IS_ACTIVE:ACTIVE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:SRM.USER_ID:MGR_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:SRM.FULL_NAME:MGR_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:proj.obj_stakeholder2:SPON_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:res_spon.ID:SPON_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:res_spon.FULL_NAME:SPONSOR@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:program.program:program@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:program.parent_id:program_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.percent_complete:percent_complete@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.prGuidelines:strat_inicijativa@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:odf.hp_priority2:hp_priority2@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:odf.hp_project_status:hp_project_status@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:unit.id:project_unit_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:unit.name:project_unit_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT(inv.schedule_start):schedule_start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT(inv.schedule_finish):schedule_finish@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.STATUS_INDICATOR:STATUS_INDICATOR@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:inv.STAGE_CODE:Stage_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:appr.name:Stage_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:late.days_late:Days_late@,
@SELECT:METRIC:USER_DEF:IMPLIED:late.days_late:Days_late_sl@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT( BaseL.start_date):base_start@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:@DBUSER@.CAL_TRUNC_DATE_FCT( BaseL.finish_date):base_finish@,
 @SELECT:DIM_PROP:USER_DEF:IMPLIED:mngprojects:prj.prusertext1:project_code@

FROM inv_investments inv
INNER JOIN INV_PROJECTS PRJ ON PRJ.PRID = INV.ID
AND @NVL@(PRJ.IS_TEMPLATE,0) = 0

inner join COP_inv_DAYS_LATE_V  late on inv.id=late.investment_id


INNER JOIN ODF_CA_PROJECT ODF ON ODF.ID = INV.ID

LEFT OUTER JOIN PRJ_OBS_ASSOCIATIONS ASSOC ON INV.ID = ASSOC.RECORD_ID
AND    ASSOC.TABLE_NAME = 'SRM_PROJECTS'

LEFT OUTER JOIN PRJ_OBS_UNITS_FLAT FLAT ON ASSOC.UNIT_ID = FLAT.UNIT_ID

LEFT OUTER JOIN PRJ_OBS_UNITS UNIT ON FLAT.UNIT_ID = UNIT.ID

LEFT OUTER JOIN CMN_LOOKUPS_V APPR
ON APPR.LOOKUP_CODE = inv.stage_code
AND APPR.LOOKUP_TYPE = 'INV_STAGE_TYPE'
AND APPR.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@

 

left outer JOIN SRM_RESOURCES SRM
ON inv.manager_id = SRM.USER_ID

LEFT OUTER JOIN prj_baseline_details BaseL ON BaseL.IS_Current = 1
AND BaseL.Object_Type = 'PROJECT'
AND BaseL.OBJECT_ID =  inv.id

inner join odf_ca_project proj on inv.id=proj.id

inner join srm_resources res_spon on proj.obj_stakeholder2=res_spon.id

inner join v_hp_program_for_portlet program on inv.id=program.project_id

 

where @FILTER@
AND (@WHERE:SECURITY:INV:INV.ID@)
AND    ((FLAT.BRANCH_UNIT_ID IS NULL) OR (@WHERE:PARAM:USER_DEF:INTEGER:FLAT.BRANCH_UNIT_ID:PROJECT_OBS@))

 

What should be changed?

 

Thank you!

Outcomes