AnsweredAssumed Answered

Error:  NPT-217: This query produced duplicate dimensional data.

Question asked by Steve_Walther on Feb 16, 2018
Latest reply on Mar 5, 2018 by Steve_Walther

Hello everyone

I am relatively new to SQL and NSQL. I’ve created a query in Jaspersoft Studio which provides detailed information out of the assignment and the task. Inside Studio the result is shown as wanted and during the setup of the Query inside CA PPM didn’t occur any error message. I’ve tried to change the query, so the error doesn’t happen again. Unfortunately, I do not know any further. Can someone tell me what I could do to correct the error?

Following the query:

SELECT
inv.CODE AS projekt_id
,inv.IS_ACTIVE
,ip.IS_TEMPLATE
,oi.Z_DIVISION AS divison
,op.Z_PPMID AS ppm_id
,CASE pt.PRSTATUS
    WHEN 0 THEN 'geplant'
    WHEN 1 THEN 'freigegeben'
    WHEN 2 THEN 'abgeschlossen' END AS phasenstatus
,dtv.Z_PROCESS_CAPTION AS Projektprozess
,pt.PRNAME AS phasenname
,pt.PRMETHODEXTID AS phasen_id
,NVL(ot.Z_PSP_PHASE, 0) AS psp
,to_char(pt.PRSTART, 'dd.mm.YYYY') AS ende_phase
,to_char(pt.PRFINISH, 'dd.mm.YYYY') AS start_phase
,CASE WHEN pka_annual.RESSOURCE ='IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) THEN NVL(PKA_ANNUAL.PKA_PAST_YEAR_IT,0) END PKA_PAST_YEAR_IT
,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) THEN NVL(PKA_ANNUAL.PKA_PAST_YEAR_BUS,0) END PKA_PAST_YEAR_BUS
,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM SYSDATE) THEN NVL(PKA_ANNUAL.PKA_CURRENT_YEAR_IT,0) END PKA_CURRENT_YEAR_IT
,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM SYSDATE) THEN NVL(PKA_ANNUAL.PKA_CURRENT_YEAR_BUS,0) END PKA_CURRENT_YEAR_BUS
,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_1_IT,0) END PKA_YEAR_1_IT
,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_1_BUS,0) END PKA_YEAR_1_BUS
,CASE WHEN pka_annual.RESSOURCE = 'IT' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_2_IT,0) END PKA_YEAR_2_IT
,CASE WHEN pka_annual.RESSOURCE = 'Business' AND pka_annual.P_YEAR = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) THEN NVL(PKA_ANNUAL.PKA_YEAR_2_BUS,0) END PKA_YEAR_2_BUS
FROM INV_INVESTMENTS inv
JOIN INV_PROJECTS ip ON inv.ID = ip.PRID
JOIN ODF_CA_PROJECT op ON inv.ID = op.ID
JOIN ODF_CA_INV oi ON inv.ID = oi.ID
JOIN ODF_TASK_V dtv ON inv.ID = dtv.PRPROJECTID
JOIN PRTASK pt ON inv.ID = pt.PRPROJECTID
JOIN ODF_CA_TASK ot ON pt.PRID = ot.ID





left Join(    select distinct
    INV.ID AS INV_ID
    ,PT.PRID
    ,srm.FULL_NAME ressource
    ,extract(year from sl.slice_date) p_year
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_PAST_YEAR_IT
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_PAST_YEAR_BUS
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_CURRENT_YEAR_IT
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_CURRENT_YEAR_BUS
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'IT' then pka_wert.PKA END) PKA_YEAR_1_IT
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_YEAR_1_BUS
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA END) PKA_YEAR_2_IT
    ,SUM(CASE WHEN pka_wert.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA END) PKA_YEAR_2_BUS
    
    from inv_investments inv
    join PRTASK pt on inv.id = pt.PRPROJECTID
    JOIN PRASSIGNMENT pa ON pt.PRID = pa.PRTASKID
    JOIN SRM_RESOURCES srm ON pa.PRRESOURCEID = srm.ID
    JOIN PRJ_BLB_SLICES SL ON pa.PRID = SL.PRJ_OBJECT_ID AND sl.slice_date BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 7*12)-1
    JOIN PRJ_BLB_SLICEREQUESTS SR ON SL.SLICE_REQUEST_ID = SR.ID




    Join(    SELECT
        pt.prid
        ,round(sum(sl.slice),0) pka
        ,extract(YEAR FROM sl.slice_date) slice_date
        ,srm.FULL_NAME
        from PRTASK pt
        join PRASSIGNMENT pa on pt.PRID = pa.PRTASKID
        join SRM_RESOURCES srm on pa.PRRESOURCEID = srm.ID
        JOIN PRJ_BLB_SLICES SL ON pa.PRID = SL.PRJ_OBJECT_ID
        JOIN PRJ_BLB_SLICEREQUESTS sr ON SL.SLICE_REQUEST_ID = SR.ID
        WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'
        GROUP BY
        sl.slice_date
        ,pt.prid
        ,srm.FULL_NAME
        ,sl.slice
    ) pka_wert on pka_wert.PRID = pt.PRID




    WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'
    AND sl.slice_date BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 7*12)-1




    GROUP BY
    inv.id
    ,srm.FULL_NAME
    ,sl.slice_date
    ,pt.PRID
    ) pka_annual ON pt.PRID = pka_annual.PRID




WHERE inv.IS_ACTIVE = 1
AND ip.IS_TEMPLATE = 0
AND oi.Z_DIVISION = 'P'
AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben'




GROUP BY inv.CODE
,inv.IS_ACTIVE
,ip.IS_TEMPLATE
,oi.Z_DIVISION
,op.Z_PPMID
,pt.PRSTATUS
,dtv.Z_PROCESS_CAPTION
,pt.PRNAME
,pt.PRMETHODEXTID
,ot.Z_PSP_PHASE
,pt.PRSTART
,pt.PRFINISH
,pka_annual.RESSOURCE
,pka_annual.P_YEAR
,pka_annual.PKA_PAST_YEAR_IT
,pka_annual.PKA_PAST_YEAR_BUS
,pka_annual.PKA_CURRENT_YEAR_IT
,pka_annual.PKA_CURRENT_YEAR_BUS
,pka_annual.PKA_YEAR_1_IT
,pka_annual.PKA_YEAR_1_BUS
,pka_annual.PKA_YEAR_2_IT
,pka_annual.PKA_YEAR_2_BUS
ORDER BY
inv.CODE

Outcomes