Clarity

We've updated our Idea Lifecycle Stages - so I'm now cleaning up NSQL Portlet technical debt. Before I update this query - let's stop and think. There are new things like ACAs in the days since this portlet was first created. Is it possible to build something like this with an Object based provider? Simple concept - a simple Count of Active Ideas in Stage. I'm not seeing an obvious way to aggregate the count before i get to the portlet makin' - but I'll remain curious for I don't know what I don't know.

Fun spike. It would be cool to have aggregate capability in a portlet. Didn't find an object based solution so I simply updated the old NSQL.

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:IDEA:(SELECT lookup.Name FROM CMN_LOOKUPS_V lookup WHERE lookup.LANGUAGE_CODE = 'en' AND lookup.LOOKUP_CODE = I.STAGE_CODE AND lookup.LOOKUP_TYPE = 'INV_STAGE_TYPE'):STAGE@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:Count (I.NAME):COUNT@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:IDEA:(CASE WHEN I.STAGE_CODE = 'osuwmc_intake' THEN 1 WHEN I.STAGE_CODE = 'osuwmc_scope' THEN 2 WHEN I.STAGE_CODE = 'osuwmc_review' THEN 3 WHEN I.STAGE_CODE = 'osuwmc_schedule' THEN 4 WHEN I.STAGE_CODE = 'osumc_scheduled' THEN 5 END):SORT_ORDER@
FROM INV_INVESTMENTS I
WHERE
@WHERE:SECURITY:PROJECT:I.ID@
AND I.PROCESS_CODE = 'osuwmc_idea_mgmt_lifecycle'
AND I.ODF_OBJECT_CODE = 'idea'
AND I.IS_ACTIVE = 1
AND @FILTER@
GROUP BY I.STAGE_CODE