Clarity

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

Steve, please check this link:  https://support.ca.com/us/knowledge-base-articles.TEC466930.html

When converted to NSQL, there’s a dimension key produced – this dimension key likely has the duplicate that is generating your error.  Say, you are using ProjectID as the dimension key and the query returns more than one record from the same project – you’ll get this error in NSQL where you wouldn’t have gotten it from SQL run directly on the DB.

In such cases, I’ve modified the query so that it creates a unique dimension key – sometimes by concatenating project id with task id, issue id…anything that will guarantee that my dimension key has a unique value.

Hopefully, there’s enough info here and in link provided that you can solve this on your own – it’s more fun.  If struggling, there are experts on this community that could probably provide a more direct answer after seeing your NSQL code.

Dale

Hi Dale,

Thank your for mentioning the link. I wasn't aware of the fact that the dimension key could be the problem.

Steve

The short answer is that the ID of the query results is not unique when used in NSQL. In SQL that is no problem.

If you have task level data in a record the ID cannot be investment ID as an investment may have several tasks.

Add something like

(inv.id+pt.prid) u_id

That is task ID concatenated after investment ID.

That may not be enough as you have slice data. If they are summarize at yearly level you need to make the unique ID (u_id) to differentiate between the annual data.

Hi Urmas,

I've tried your suggestions and it seems to work just as I needed it to.

Thank you a lot!

Steve

Just because you are no longer experiencing the 'duplicate errors message' does not mean your query is correct.  Potentially the only reason you are seeing correct results is because you only have one task on a project with 'AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben''.  Please see response earlier today, your query is wrong and you need to add additional join conditions.

In addition, your query for PKA_ANNUAL is also potentially incorrect.  I can't work out why you have a join PKA_WERT which uses the time slice for assignment which you are also joining in the main section of PKA_ANNUAL.  Are you sure you are getting the correct data returned, as I believe you need to review this section of your code.

Partly agree with Roland.

Not getting the error means that the query is technically correct.

However, that does not mean that it is necessarily querying and returning  exactly what you want.

Hi urmas, I believe the query is wrong.  It should not be described as "technically correct". 

Sorry for calling this out but I would hate to see someone to use it, or part of it, when searching this forum in the future, which happened to me earlier today and I found this topic.

I can't explain why Steve is getting the "results as wanted" but  I believe the additional joins are required as described earlier.

urmas

Hi everyone,

Thank you for pointing the mistake out.

Before, when I mentioned, that "the result is shown as wanted" I did not have the time to check it in detail. You both are correct, some information is in fact missing.

As I did mention in the beginning question, I am relatively new to SQL and NSQL and I am basically learning this by doing. So I am thankful to have again learnt something .

At given time I am going to test the inputs of Roland.

Again, thank both of you!

No problem. What could be more correct is that there is no NSQL syntax error.

It is your query, in particular:

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

You are not joining odf_task_v to prtask, only back to the project.  Trying adding in pt.prid = dtv.odf_pk to the second join.

Hi Steve, please consider below query - it has not been run in SQL so may not be syntonically correct, but hopefully points you closer to what you are trying to achieve.  There are lots of comments which you need to check, as for example, I believe you need to use two TSV's where as you are only pulling back one custom TSV for Assignments.

SELECT
inv.code || pt.prid unique_dim -- note this will return a string so you may need to recreate the portlet if you previously had an integer for the unique dim in NSQL - I personally never use inv.id + pt.prid - this can result in duplicates
,inv.CODE AS projekt_id
,inv.id prj_id -- can be used in the porlet to provide hyperlink back to project
,inv.IS_ACTIVE
,ip.IS_TEMPLATE
,oi.Z_DIVISION AS divison
,op.Z_PPMID AS ppm_id
,pt.prid -- can be used in the portlet to provide hyperlink back to the task
,CASE pt.PRSTATUS
    WHEN 0 THEN 'geplant'
    WHEN 1 THEN 'freigegeben'
    WHEN 2 THEN 'abgeschlossen' END AS phasenstatus -- you should be able to get this from odf_task_v.prstatus_caption
,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
, 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
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 and dtv.odf_user_id = 1 -- when running exernal from Clarity you need to add ' and odf_user_id = 1' or similar.  Set up the admin (id = 1) to be the required language
JOIN PRTASK pt ON inv.ID = pt.PRPROJECTID and pt.prid = dtv.odf_pk -- if you are using the above view, you don't need this in your query, leaving to simply changes to query
JOIN ODF_CA_TASK ot ON pt.PRID = ot.ID  -- if you are using the above view, you don't need this in your query, , leaving to simply changes to query
left Join(    select
    pt.PRPROJECTID
    ,PT.PRID
    ,SUM(CASE WHEN trunc(sl.slice_date,'YYYY') = trunc(ADD_MONTHS(SYSDATE,-12)'YYYY') AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_PAST_YEAR_IT  -- you need to review how you are caulculating annual years example for previous year provided here
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_PAST_YEAR_BUS  -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_CURRENT_YEAR_IT  -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM SYSDATE) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_CURRENT_YEAR_BUS -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'IT' then pka_wert.PKA else 0 END) PKA_YEAR_1_IT -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_YEAR_1_BUS -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'IT' THEN pka_wert.PKA else 0 END) PKA_YEAR_2_IT -- review, don't believe case statement correct
    ,SUM(CASE WHEN sl.slice_date = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,2*12)) AND pka_wert.FULL_NAME = 'Business' THEN pka_wert.PKA else 0 END) PKA_YEAR_2_BUS -- review, don't believe case statement correct
    from PRTASK pt
    JOIN PRASSIGNMENT pa ON pt.PRID = pa.PRTASKID
    join srm_resources srm on srm.id = pa.prresourceid
    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  -- you are pulling back 7 years work of data, and none for the previous year - you need to review what months are required
    JOIN PRJ_BLB_SLICEREQUESTS SR ON SL.SLICE_REQUEST_ID = SR.ID
    WHERE sr.request_name = 'assignment::z_pka_betrag::dwh_month'  -- NOTE, you need to review as Assignment TSV is broken down by either Actual or ETC - given that you are searching for Past (which will be actuals) and Future (which will be ETC) you may need to pull back data from two TSV
    GROUP BY
    pt.PRPROJECTID
    ,pt.PRID
    ) pka_annual ON pt.prprojectid = pka_annual.ptprojectid and pt.prid = pka_annual.prid
WHERE inv.IS_ACTIVE = 1 -- make this a filter in the portlet rather than harcoding here
AND ip.IS_TEMPLATE = 0
AND oi.Z_DIVISION = 'P'
AND dtv.Z_PROCESS_CAPTION = 'IT Vorhaben' -- make this a filter in the portlet rather than hardcoding here 

urmas

Hi everyone,

Sorry for my late response, I was out of office the last two weeks.

I do agree with the fact that the query is wrong. The query delivers multidimensional data.

Thank you Roland for giving me the code with your comments in it. I will test your suggestions and correct the query.

As far as the beginning question goes, I would consider it as solved.

The answer - the query is wrong.

Kind Regards,

Steve