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