Reputation: 89
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