Skip to Content
0
Former Member
Dec 11, 2013 at 12:40 AM

Inconsistent aggregation behavior of Calc View

25 Views

Hi All,

HANA outputs incorrect results for SQL against Calculation View. The revision is 67.

Please look at the below SQL against Calculation view CA_DELIVERY_PERF

SELECT COUNT(*) as SP0_COUNT FROM "_SYS_BIC"."ssc.sales/CA_DELIVERY_PERF"

WHERE REGION = 'AMERICAS' AND

FISCALPERIOD = '2012005' AND

SCHEDULE_GROUP = 'MTS' AND

( STP_IND = 'H0' OR STP_IND = 'E' )

Result : SP0_COUNT = 1,104

As per logic, the below two SQL statements should give the same result for SP0_COUNT as the above statement.

SELECT REGION, FISCALPERIOD, SCHEDULE_GROUP, SUM(ITEM_COUNT),

SUM(CASE "STP_IND" WHEN 'E' THEN 1 WHEN 'H0' THEN 1 END) AS SPO_COUNT

FROM "_SYS_BIC"."ssc.sales/CA_DELIVERY_PERF"

WHERE REGION = 'AMERICAS' AND

FISCALPERIOD = '2012005' AND

SCHEDULE_GROUP = 'MTS' AND

( STP_IND = 'H0' OR STP_IND = 'E' )

GROUP BY REGION, FISCALPERIOD, SCHEDULE_GROUP

Result : SP0_COUNT = 83

SELECT P.REGION AS REGION, P.FISCALPERIOD AS FISCALPERIOD, P.SCHEDULE_GROUP AS SCHEDULE_GROUP, SUM(P.ITEM_COUNT) AS ITEM_COUNT,

(SELECT COUNT(1) FROM "_SYS_BIC"."ssc.sales/CA_DELIVERY_PERF" WHERE ("STP_IND" = 'E' OR "STP_IND" = 'H0') AND REGION = P.REGION AND

FISCALPERIOD = P.FISCALPERIOD AND

SCHEDULE_GROUP = P.SCHEDULE_GROUP ) AS SPO_COUNT

FROM "_SYS_BIC"."ssc.sales/CA_DELIVERY_PERF" AS P

WHERE REGION = 'AMERICAS' AND

FISCALPERIOD = '2012005' AND

SCHEDULE_GROUP = 'MTS'

GROUP BY REGION, FISCALPERIOD, SCHEDULE_GROUP

Result : SP0_COUNT = 83

Please let me know why we are facing the inconsistency in the results for the same SQL logic.

Goutham