SQL Curve portlet best practices for querying

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

You would like to create a custom portlet with SQL curve period data and make sure the portlet has optimal performance. 

Environment

All Supported Clarity Releases

Resolution

We recommend using the function nk_sum_slices_row_fct for optimal performance with SQL Curve periods

Example queries you can use to adapt to your use case: 

Note : You must replace the entity "Corporate" with the entity name you are using on your system

 

with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate') slices_calendar from dual )
SELECT prid, slices.*
FROM prteam, nk_sum_slices_row_fct(odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) slices

with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prid, slices.*
FROM prj_resources, nk_sum_slices_row_fct(odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) slices 

with slices_calendar as (select CRV_CALENDAR_FCT('quarters',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate') slices_calendar from dual )
SELECT prid, slices.*
FROM prteam, nk_sum_slices_row_fct(odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) slices

with slices_calendar as (select CRV_CALENDAR_FCT('months',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,100,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prteam.prid, avail_slices.ts1 avail_ts1, avail_slices.ts2 avail_ts2, alloc_slices.ts1 alloc_ts1, alloc_slices.ts2 alloc_ts2, avail_slices.ts1 - alloc_slices.ts1 remain_ts1
FROM prteam, prj_resources
, nk_sum_slices_row_fct(prj_resources.odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) avail_slices 
, nk_sum_slices_row_fct(prteam.odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) alloc_slices 
WHERE prteam.prresourceid = prj_resources.prid

with slices_calendar as (select CRV_CALENDAR_FCT('months',TO_DATE('2024-01-01','YYYY-MM-DD'),NULL,12,'default',',ENTITY=Corporate,hours') slices_calendar from dual )
SELECT prj_resources.prid, max(avail_slices.ts1) avail_ts1, max(avail_slices.ts2) avail_ts2, sum(alloc_slices.ts1) alloc_ts1, sum(alloc_slices.ts2) alloc_ts2, count(*) cnt
FROM prteam, prj_resources
, nk_sum_slices_row_fct(prj_resources.odf_nk_odf_pravailcurve, (select slices_calendar from slices_calendar)) avail_slices 
, nk_sum_slices_row_fct(prteam.odf_nk_odf_pralloccurve, (select slices_calendar from slices_calendar)) alloc_slices 
WHERE prteam.prresourceid = prj_resources.prid 
GROUP BY prj_resources.prid 

Additional Information

Note: When the periods are set to "default" then what you set for entity does not matter it will return all the data. If the period is fiscal, then it will need the correct entity or else it returns nulls.