Knight Shao
Knight Shao

Reputation: 89

SQLs with Grouping Set do not return the same result

I have 2 queries with Grouping set. Before doing grouping set, I have checked the result are totally the same. The values of L_TIM_53_ID and L_TIM_54_ID are always 2458120. But I got a different result with the following 2 Queries.

Query A,

SELECT NVL(TABLE_A.PRD_LVL_MEMBER_ID,2147483647) AS L_PRD_1_ID
,NVL(TABLE_A.TIM_52_ID,2147483647) AS L_TIM_52_ID
,NVL(TABLE_A.TIM_53_ID,2147483647) AS L_TIM_53_ID
,NVL(TABLE_A.TIM_54_ID,2147483647) AS L_TIM_54_ID
,sum(ADJ_BOOK_HISTORY) AS M_ADJ_BOOK_HISTORY
FROM (SELECT TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID,DCS_LVL_MEMBER_ID,PRD_LVL_MEMBER_ID,CUS_LVL_MEMBER_ID,ORG_LVL_MEMBER_ID FROM TABLE_A,(SELECT TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID
FROM MSC_DIMENSION_TIM_DYD
WHERE ((MSC_DIMENSION_TIM_DYD.TIM_53_ID IN (2458120)) AND (MSC_DIMENSION_TIM_DYD.TIM_54_ID IN (2458120)))
AND TIM_LEVEL_ID = 51
AND AGGR_TIM_LEVEL_ID = 51
GROUP BY TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID) MISSING_DATES) TABLE_A
,TABLE_B
WHERE TABLE_A.TIM_LVL_MEMBER_ID = TABLE_B.TIM_LVL_MEMBER_ID(+)
AND TABLE_A.DCS_LVL_MEMBER_ID = TABLE_B.DCS_LVL_MEMBER_ID(+)
AND TABLE_A.PRD_LVL_MEMBER_ID = TABLE_B.PRD_LVL_MEMBER_ID(+)
AND TABLE_A.CUS_LVL_MEMBER_ID = TABLE_B.CUS_LVL_MEMBER_ID(+)
AND TABLE_A.ORG_LVL_MEMBER_ID = TABLE_B.ORG_LVL_MEMBER_ID(+)
GROUP BY GROUPING SETS (
(TABLE_A.TIM_54_ID)
,(TABLE_A.PRD_LVL_MEMBER_ID,TABLE_A.TIM_52_ID,TABLE_A.TIM_53_ID,TABLE_A.TIM_54_ID)
)

Query B,

SELECT NVL(TABLE_A.PRD_LVL_MEMBER_ID,2147483647) AS L_PRD_1_ID
,NVL(TABLE_A.TIM_52_ID,2147483647) AS L_TIM_52_ID
,NVL(TABLE_A.TIM_53_ID,2147483647) AS L_TIM_53_ID
,NVL(TABLE_A.TIM_54_ID,2147483647) AS L_TIM_54_ID
,sum(ADJ_BOOK_HISTORY) AS M_ADJ_BOOK_HISTORY
FROM (SELECT TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID,DCS_LVL_MEMBER_ID,PRD_LVL_MEMBER_ID,CUS_LVL_MEMBER_ID,ORG_LVL_MEMBER_ID FROM TABLE_A,(SELECT TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID
FROM MSC_DIMENSION_TIM_DYD
WHERE ((MSC_DIMENSION_TIM_DYD.TIM_53_ID IN (2458120)))
AND TIM_LEVEL_ID = 51
AND AGGR_TIM_LEVEL_ID = 51
GROUP BY TIM_LEVEL_ID,TIM_LVL_MEMBER_ID,TIM_51_ID,TIM_54_ID,TIM_53_ID,TIM_52_ID) MISSING_DATES) TABLE_A
,TABLE_B
WHERE TABLE_A.TIM_LVL_MEMBER_ID = TABLE_B.TIM_LVL_MEMBER_ID(+)
AND TABLE_A.DCS_LVL_MEMBER_ID = TABLE_B.DCS_LVL_MEMBER_ID(+)
AND TABLE_A.PRD_LVL_MEMBER_ID = TABLE_B.PRD_LVL_MEMBER_ID(+)
AND TABLE_A.CUS_LVL_MEMBER_ID = TABLE_B.CUS_LVL_MEMBER_ID(+)
AND TABLE_A.ORG_LVL_MEMBER_ID = TABLE_B.ORG_LVL_MEMBER_ID(+)
GROUP BY GROUPING SETS (
(TABLE_A.TIM_54_ID)
,(TABLE_A.PRD_LVL_MEMBER_ID,TABLE_A.TIM_52_ID,TABLE_A.TIM_53_ID,TABLE_A.TIM_54_ID)
)

Result for Query A,

12143       2458120     2458120     2458120     25.4794520578945205476403721461486967
12143       2458151     2458120     2458120     23.0136986329369863010945296803923712
12143       2458179     2458120     2458120     25.4794520578945205476403721461486967
2147483647  2147483647  2147483647  2147483647  73.9726027487260273963752739726897647

Result for Query B,

12143       2458120     2458120     2458120 25.47945205789452054764037214614869673869
12143       2458151     2458120     2458120 23.01369863293698630109452968039237124785
12143       2458179     2458120     2458120 25.47945205789452054764037214614869673869
2147483647  2147483647  2147483647  2458120 73.97260274872602739637527397268976472523

We can clearly see the issued group set is for TABLE_A.TIM_54_ID, I don't know what is the difference between these 2 SQL. Why they return the different results?

Upvotes: 1

Views: 268

Answers (0)

Related Questions