Hi Gurus,
I have below query with UNION. it scan same table twice. just wondering if it is possible to re-write without union
WITH T
AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
UNION ALL
SELECT 'BBB', 200 FROM DUAL
UNION ALL
SELECT 'BBB', 100 FROM DUAL
UNION ALL
SELECT 'BBB', 300 FROM DUAL
UNION ALL
SELECT 'CCC', 444 FROM DUAL
UNION ALL
SELECT 'CCC', 333 FROM DUAL
UNION ALL
SELECT 'DDD', 230 FROM DUAL
UNION ALL
SELECT 'DDD', 240 FROM DUAL
UNION ALL
SELECT 'EEE', 140 FROM DUAL)
SELECT CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END AS DES, SUM (AMT)
FROM T
WHERE DESCR <> 'AAA'
GROUP BY CASE WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER' ELSE 'BIZ_NER' END
UNION ALL
SELECT DESCR, SUM (AMT)
FROM T
GROUP BY DESCR;
WITH T
AS (SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
UNION ALL
SELECT 'BBB', 200 FROM DUAL
UNION ALL
SELECT 'BBB', 100 FROM DUAL
UNION ALL
SELECT 'BBB', 300 FROM DUAL
UNION ALL
SELECT 'CCC', 444 FROM DUAL
UNION ALL
SELECT 'CCC', 333 FROM DUAL
UNION ALL
SELECT 'DDD', 230 FROM DUAL
UNION ALL
SELECT 'DDD', 240 FROM DUAL
UNION ALL
SELECT 'EEE', 140 FROM DUAL)
SELECT descr, SUM (AMT)
FROM T
GROUP BY GROUPING SETS (
(CASE
WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
WHEN DESCR <> 'AAA' THEN'BIZ_NER'
END) ,
DESCR)
I tried to use grouping set like below, but I am not able to fix these two issues: as you can see, 1. the underlined record is extra (need to be removed) ; 2, the column name missed for these two record which is case statement in grouping set.
Is it possible to re-write the query without union?
thanks in advance.
the output like below:
DESCR SUM(AMT)
100 1847
140CCC 777
DDD 470
EEE 140
BBB 600
AAA 100
expected result is:
DES SUM(AMT)
PER_NER 1847
BIZ_NER 140CCC 777
DDD 470
EEE 140
BBB 600
AAA 100
Sure it is.
You need to include the case expression in your select list as well as the group by.
The key is to return this expression when the description is null (e.g. with nvl/coalesce). And exclude the rows where the result of this null from the final results.
For example:
WITH T AS (
SELECT 'AAA' AS DESCR, 100 AS AMT FROM DUAL
UNION ALL
SELECT 'BBB', 200 FROM DUAL
UNION ALL
SELECT 'BBB', 100 FROM DUAL
UNION ALL
SELECT 'BBB', 300 FROM DUAL
UNION ALL
SELECT 'CCC', 444 FROM DUAL
UNION ALL
SELECT 'CCC', 333 FROM DUAL
UNION ALL
SELECT 'DDD', 230 FROM DUAL
UNION ALL
SELECT 'DDD', 240 FROM DUAL
UNION ALL
SELECT 'EEE', 140 FROM DUAL
), grps as (
SELECT COALESCE (
descr,
CASE
WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
WHEN DESCR <> 'AAA' THEN'BIZ_NER'
END
) descr,
SUM (AMT)
FROM T
GROUP BY GROUPING SETS (
(CASE
WHEN DESCR IN ('BBB', 'CCC', 'DDD') THEN 'PER_NER'
WHEN DESCR <> 'AAA' THEN'BIZ_NER'
END) ,
DESCR)
)
SELECT * FROM grps
WHERE descr IS NOT NULL;
DESCR SUM(AMT)
EEE 140
DDD 470
AAA 100
CCC 777
BBB 600
PER_NER 1847
BIZ_NER 140
Note you don't need grouping sets in this example. Rollup works just fine here.