Clarity

Expand all | Collapse all

NSQL Query -- at least one null dimension key.

  • 1.  NSQL Query -- at least one null dimension key.

    Posted 01-15-2019 11:58 AM

    Good Day, 

     

    I have a NSQL query which returns the following error "The query used by this portlet produced at least one null dimension key."  I am puzzled by this as my dimension key is a ROW_COUNT() value and when I utilize a test version of the query in SQL Server (in TSQL format) I do not see any NULL value in the key or the return dataset.  I am hoping that the members here can show me where my error is and also explain why the error exists so that I can successfully execute this query as a chart portlet (stacked bar).  Based on input into PPM (v15.3) I know that the NSQL is syntactically correct.

     

    Thank you in advance

     

    --NSQL Query

    SELECT
    @select:dim:user_def:implied:StBar:OuterStackedBar.UniqueRowID:UniqueRowID@,
    @select:dim_prop:user_def:implied:StBar:OuterStackedBar.StatusStage:StatusStage@,
    @select:dim_prop:user_def:implied:StBar:OuterStackedBar.PrjOverallStatusColor:PrjStatusColor@,
    @select:dim_prop:user_def:implied:StBar:OuterStackedBar.prjStageName:prjStageName@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenClosing:GreenClosingSum:GreenClosing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenDiscovery:GreenDiscoverySum:GreenDiscovery@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenIdea:GreenIdeaSum:GreenIdea@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenImplementing:GreenImplementingSum:GreenImplementing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenInitiation:GreenInitiationSum:GreenInitiation@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.GreenNotStarted:GreenNotStartedSum:GreenNotStarted@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowClosing:YellowClosingSum:YellowClosing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowDiscovery:YellowDiscoverySum:YellowDiscovery@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowIdea:YellowIdeaSum:YellowIdea@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowImplementing:YellowImplementingSum:YellowImplementing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowInitiation:YellowInitiationSum:YellowInitiation@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.YellowNotStarted:YellowNotStartedSum:YellowNotStarted@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedClosing:RedClosingSum:RedClosing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedDiscovery:RedDiscoverySum:RedDiscovery@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedIdea:RedIdeaSum:RedIdea@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedImplementing:RedImplementingSum:RedImplementing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedInitiation:RedInitiationSum:RedInitiation@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.RedNotStarted:RedNotStartedSum:RedNotStarted@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedClosing:NoStatusDefinedClosingSum:NoStatusDefinedClosing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedDiscovery:NoStatusDefinedDiscoverySum:NoStatusDefinedDiscovery@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedIdea:NoStatusDefinedIdeaSum:NoStatusDefinedIdea@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedImplementing:NoStatusDefinedImplementingSum:NoStatusDefinedImplementing@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedInitiation:NoStatusDefinedInitiationSum:NoStatusDefinedInitiation@,
    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NoStatusDefinedNotStarted:NoStatusDefinedNotStartedSum:NoStatusDefinedNotStarted@

     

    FROM (
    SELECT
    PrjOverallstatusColor + '-' + StageName as StatusStage
    , ROW_NUMBER() OVER(ORDER BY PrjOverallstatusColor + '-' + StageName ASC) AS UniqueRowID
    , prjOverallStatusColor
    ,StageName as prjStageName
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Closing' then 1 else 0 end) as GreenClosing
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Discovery' then 1 else 0 end) as GreenDiscovery
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Idea' then 1 else 0 end) as GreenIdea
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Implementing' then 1 else 0 end) as GreenImplementing
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Initiation' then 1 else 0 end) as GreenInitiation
    ,sum(CASE When PrjOverallstatusColor = 'green' and StageName = 'Not Started' then 1 else 0 end) as GreenNotStarted

    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Closing' then 1 else 0 end) as YellowClosing
    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Discovery' then 1 else 0 end) as YellowDiscovery
    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Idea' then 1 else 0 end) as YellowIdea
    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Implementing' then 1 else 0 end) as YellowImplementing
    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Initiation' then 1 else 0 end) as YellowInitiation
    ,sum(CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Not Started' then 1 else 0 end) as YellowNotStarted

    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Closing' then 1 else 0 end) as RedClosing
    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Discovery' then 1 else 0 end) as RedDiscovery
    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Idea' then 1 else 0 end) as RedIdea
    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Implementing' then 1 else 0 end) as RedImplementing
    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Initiation' then 1 else 0 end) as RedInitiation
    ,sum(CASE When PrjOverallstatusColor = 'Red' and StageName = 'Not Started' then 1 else 0 end) as RedNotStarted

    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Closing' then 1 else 0 end) as NoStatusDefinedClosing
    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Discovery' then 1 else 0 end) as NoStatusDefinedDiscovery
    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Idea' then 1 else 0 end) as NoStatusDefinedIdea
    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Implementing' then 1 else 0 end) as NoStatusDefinedImplementing
    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Initiation' then 1 else 0 end) as NoStatusDefinedInitiation
    ,sum(CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Not Started' then 1 else 0 end) as NoStatusDefinedNotStarted


    FROM
    inv_investments i
    INNER JOIN inv_projects p ON i.id = p.prID
    INNER JOIN odf_cop_prj_statusrpt_v2 sr ON i.id = sr.odf_parent_id
    LEFT OUTER JOIN cop_prj_statusrpt_latest_v lsr ON sr.odf_pk = lsr.report_id

    LEFT OUTER JOIN (
    SELECT distinct
    inv.code,
    sr.cop_report_date,
    sr.odf_pk,
    s.LOOKUP_CODE as Stage_Code,
    ISNULL(s.name, 'No Stage Defined') as StageName ,

    ISNULL(CONVERT(VARCHAR(30),sr.ph_project_status), 'No PH Project Status Defined') as PrjOverallStatus,
    CASE sr.ph_project_status
    when 1 then 'Green'
    when 2 then 'Yellow'
    when 3 then 'Red'
    else 'No Status Defined'
    end AS PrjOverallStatusColor


    FROM
    inv_investments inv LEFT JOIN odf_ca_inv bc
    ON inv.ID = bc.id
    INNER JOIN odf_ca_project pc
    ON inv.id = pc.id
    INNER JOIN odf_cop_prj_statusrpt_v2 sr
    ON inv.id = sr.odf_parent_id
    LEFT OUTER JOIN cmn_lookups_v s
    ON inv.stage_code = s.lookup_code
    AND s.lookup_type = 'INV_STAGE_TYPE'
    AND s.language_code = 'en'
    WHERE
    INV.CODE LIKE 'Prj-%'
    AND INV.is_active = 1
    and sr.odf_PK in ( select MaxReportKey
    from (
    SELECT inner_inv.code, max(odf_pk) AS MaxReportKey
    FROM inv_investments inner_inv INNER JOIN odf_cop_prj_statusrpt_v2 sr
    ON inner_inv.id = sr.odf_parent_id
    WHERE inner_inv.IS_ACTIVE = 1
    GROUP BY inner_inv.code
    ) as A
    )
    GROUP BY
    inv.code,
    sr.cop_report_date,
    sr.odf_pk,
    s.LOOKUP_CODE,
    ISNULL(s.name, 'No Stage Defined'),
    ISNULL(CONVERT(VARCHAR(30),sr.ph_project_status), 'No PH Project Status Defined'),
    CASE sr.ph_project_status
    when 1 then 'Green'
    when 2 then 'Yellow'
    when 3 then 'Red'
    else 'No Status Defined'
    end
    ) PrjInfo
    on i.Stage_Code = PrjInfo.Stage_Code

    WHERE i.is_active = 1
    AND p.is_template = 0
    AND lsr.report_order = 1
    AND (p.is_program = 0 OR @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:program@,0) = 1)
    AND (@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0) = 0 OR
    i.id IN (SELECT obsa.record_id
    FROM prj_obs_associations obsa, prj_obs_units_flat obsf
    WHERE obsa.unit_id = obsf.unit_id
    AND obsa.table_name = 'SRM_PROJECTS'
    and (StageName is not null and PrjOverallStatusColor IS NOT NULL)
    AND obsf.branch_unit_id = @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0)))
    AND @WHERE:SECURITY:PROJECT:i.id@
    AND @FILTER@

    GROUP BY I.CODE, PrjInfo.cop_report_date, PrjInfo.odf_pk, PrjInfo.Stage_Code, PrjInfo.StageName, PrjInfo.PrjOverallStatus, PrjInfo.PrjOverallStatusColor
    ) OuterStackedBar

    GROUP By PrjOverallStatusColor, prjStageName, statusstage,
    GreenClosing, GreenDiscovery, GreenIdea, GreenImplementing, GreenInitiation, GreenNotStarted,
    YellowClosing, YellowDiscovery, YellowIdea, YellowImplementing, YellowInitiation, YellowNotStarted,
    RedClosing, RedDiscovery, RedIdea, RedImplementing, RedInitiation, RedNotStarted,
    NoStatusDefinedClosing, NoStatusDefinedDiscovery, NoStatusDefinedIdea, NoStatusDefinedImplementing,
    NoStatusDefinedInitiation, NoStatusDefinedNotStarted, UniqueRowID

     



  • 2.  Re: NSQL Query -- at least one null dimension key.

    Posted 01-16-2019 04:28 AM

    Sounds odd.

     

    Can you convert your NSQL into SQL and run it directly against the database with the same parameters as you are running it with when you see the "error". That way you could see the actual data that is being returned by the query and possibly spot what it is complaining about.



  • 3.  Re: NSQL Query -- at least one null dimension key.

    Posted 01-16-2019 11:17 AM

    David, 

    Thank you for your reply.  I apologize if I was not clear in my question where I meant to say that I have a "converted" copy that I ran directly against the database.  It did return 25 records and the dimension key field (UniqueRowID) was consistent and contained no nulls.

     

    Originally, I had planned on utilizing the field StatusStage (concantenated field value) to be my key.  However, I original received the error/warning with that field.  After reading some other chains I felt It may be best to try a more simplistic key field using the Row_Number() value to absolutely ensure there would be no NULL values returned.  

     

    MT



  • 4.  Re: NSQL Query -- at least one null dimension key.

    Posted 01-16-2019 12:11 PM

    Definitely sounds odd* then

    I wonder if there are any known issues with 15.3 around this - someone from CA would have to tell you that I think though.

     

    * - i.e. wrong / bug



  • 5.  Re: NSQL Query -- at least one null dimension key.

    Posted 01-16-2019 01:22 PM

    Can you modify the SQL and NSQL to be without grouping to see if that provides more info?



  • 6.  Re: NSQL Query -- at least one null dimension key.

    Posted 01-25-2019 06:46 PM

    Sorry, another project took me away for a bit.  I rewrote the original query (in SQL Server) and came up with a data set which is 4 Rows by 6 Columns. More importantly no null values anywhere in the data set.  It is a nice tight package of data that we want to produce a stacked bar graph portlet to view.

     

    However, once again I have received the original error (note, I still have a counting error--counting too many) but that is a refinement I need to make in the base query.  My original question remains.  I am relatively sure it is in my definition (NSQL).  I think it may be the very outside group by.  However, when validating the NSQL I needed to add every column?  Maybe I do not require any group by (very last line of the query)?

     

    Thank you all for looking.

     

    The new query follows:

     

    SELECT

    @select:dim:user_def:implied:StBar:OuterStackedBar.StatusStage:StatusStage@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.Idea:Idea:Idea@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.Discovery:Disovery:Discovery@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.Initiation:Initiation:Initiation@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.Implementing:Implementing:Implementing@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.Closing:Closing:Closing@,

    @SELECT:METRIC:USER_DEF:IMPLIED:OuterStackedBar.NotStarted:NotStarted:NotStarted@

    from (

    --near sql

    SELECT

    a.StatusStage,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenIdea)

    WHEN 'red' THEN SUM(redIdea)

    WHEN 'yellow' THEN SUM(yellowIdea)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedIdea)

    END AS Idea,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenDiscovery)

    WHEN 'red' THEN SUM(redDiscovery)

    WHEN 'yellow' THEN SUM(yellowDiscovery)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedDiscovery)

    END as Discovery,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenInitiation)

    WHEN 'red' THEN SUM(redInitiation)

    WHEN 'yellow' THEN SUM(yellowInitiation)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedInitiation)

    END AS Initiation,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenImplementing)

    WHEN 'red' THEN SUM(redImplementing)

    WHEN 'yellow' THEN SUM(yellowImplementing)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedImplementing)

    END AS Implementing,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenclosing)

    WHEN 'red' THEN SUM(redclosing)

    WHEN 'yellow' THEN SUM(yellowclosing)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedClosing)

    END AS Closing,

    CASE a.StatusStage

    WHEN 'green' THEN SUM(greenNotStarted)

    WHEN 'red' THEN SUM(redNotStarted)

    WHEN 'yellow' THEN SUM(yellowNotStarted)

    WHEN 'No Status Defined' THEN SUM(NoStatusDefinedNotStarted)

    END AS NotStarted

    FROM

    (

    SELECT

    PrjOverallStatusColor as StatusStage

    , ROW_NUMBER() OVER(ORDER BY PrjOverallstatusColor + '-' + StageName ASC) AS UniqueRowID

    , prjOverallStatusColor

    ,StageName as prjStageName

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Closing' then 1 else 0 end as GreenClosing

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Discovery' then 1 else 0 end as GreenDiscovery

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Idea' then 1 else 0 end as GreenIdea

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Implementing' then 1 else 0 end as GreenImplementing

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Initiation' then 1 else 0 end as GreenInitiation

    , CASE When PrjOverallstatusColor = 'green' and StageName = 'Not Started' then 1 else 0 end as GreenNotStarted

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Closing' then 1 else 0 end as YellowClosing

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Discovery' then 1 else 0 end as YellowDiscovery

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Idea' then 1 else 0 end as YellowIdea

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Implementing' then 1 else 0 end as YellowImplementing

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Initiation' then 1 else 0 end as YellowInitiation

    , CASE When PrjOverallstatusColor = 'Yellow' and StageName = 'Not Started' then 1 else 0 end as YellowNotStarted

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Closing' then 1 else 0 end as RedClosing

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Discovery' then 1 else 0 end as RedDiscovery

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Idea' then 1 else 0 end as RedIdea

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Implementing' then 1 else 0 end as RedImplementing

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Initiation' then 1 else 0 end as RedInitiation

    , CASE When PrjOverallstatusColor = 'Red' and StageName = 'Not Started' then 1 else 0 end as RedNotStarted

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Closing' then 1 else 0 end as NoStatusDefinedClosing

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Discovery' then 1 else 0 end as NoStatusDefinedDiscovery

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Idea' then 1 else 0 end as NoStatusDefinedIdea

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Implementing' then 1 else 0 end as NoStatusDefinedImplementing

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Initiation' then 1 else 0 end as NoStatusDefinedInitiation

    , CASE When PrjOverallstatusColor = 'No Status Defined' and StageName = 'Not Started' then 1 else 0 end as NoStatusDefinedNotStarted

     

    FROM

    inv_investments i

    INNER JOIN inv_projects p ON i.id = p.prID

    INNER JOIN odf_cop_prj_statusrpt_v2 sr ON i.id = sr.odf_parent_id

    LEFT OUTER JOIN cop_prj_statusrpt_latest_v lsr ON sr.odf_pk = lsr.report_id

    LEFT OUTER JOIN (

    SELECT distinct

    inv.code,

    sr.cop_report_date,

    sr.odf_pk,

    s.LOOKUP_CODE as Stage_Code,

    ISNULL(s.name, 'No Stage Defined') as StageName ,

     

    ISNULL(CONVERT(VARCHAR(30),sr.ph_project_status), 'No PH Project Status Defined') as PrjOverallStatus,

    CASE sr.ph_project_status

    WHEN 1 THEN 'Green'

    WHEN 2 THEN 'Yellow'

    WHEN 3 THEN 'Red'

    ELSE 'No Status Defined'

    END AS PrjOverallStatusColor

    FROM

    inv_investments inv LEFT JOIN odf_ca_inv bc

    ON inv.ID = bc.id

    INNER JOIN odf_ca_project pc

    ON inv.id = pc.id

    INNER JOIN odf_cop_prj_statusrpt_v2 sr

    ON inv.id = sr.odf_parent_id

    LEFT OUTER JOIN cmn_lookups_v s

    ON inv.stage_code = s.lookup_code

    AND s.lookup_type = 'INV_STAGE_TYPE'

    AND s.language_code = 'en'

    WHERE

    INV.CODE LIKE 'Prj-%'

    AND INV.is_active = 1

    AND sr.odf_PK IN ( SELECT MaxReportKey

    FROM (

    SELECT inner_inv.code, max(odf_pk) AS MaxReportKey

    FROM inv_investments inner_inv INNER JOIN odf_cop_prj_statusrpt_v2 sr

    ON inner_inv.id = sr.odf_parent_id

    WHERE inner_inv.IS_ACTIVE = 1

    GROUP BY inner_inv.code

    ) as A

    )

    GROUP BY

    inv.code,

    sr.cop_report_date,

    sr.odf_pk,

    s.LOOKUP_CODE,

    ISNULL(s.name, 'No Stage Defined'),

    ISNULL(CONVERT(VARCHAR(30),sr.ph_project_status), 'No PH Project Status Defined'),

    CASE sr.ph_project_status

    WHEN 1 THEN 'Green'

    WHEN 2 THEN 'Yellow'

    WHEN 3 THEN 'Red'

    ELSE 'No Status Defined'

    END

    ) PrjInfo

    ON i.Stage_Code = PrjInfo.Stage_Code

    WHERE

    i.is_active = 1

    AND p.is_template = 0

    AND lsr.report_order = 1

    AND (p.is_program = 0 OR @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:program@,0) = 1)

    AND (@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0) = 0 OR

    i.id IN (

    SELECT obsa.record_id

    FROM prj_obs_associations obsa, prj_obs_units_flat obsf

    WHERE obsa.unit_id = obsf.unit_id

    AND obsa.table_name = 'SRM_PROJECTS'

    AND (StageName IS NOT NULL AND PrjOverallStatusColor IS NOT NULL)

    AND obsf.branch_unit_id = @NVL@(@WHERE:PARAM:USER_DEF:INTEGER:obs@,0))

    )

    AND @WHERE:SECURITY:PROJECT:i.id@

    AND @FILTER@

     

    ) AS a

    GROUP BY a.StatusStage

    ) AS OuterStackedBar

    Group by StatusStage, Idea, Discovery, Initiation, Implementing, Closing, NotStarted