Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Ken.

Asked: September 10, 2020 - 10:00 pm UTC

Answered by: Chris Saxon - Last updated: September 11, 2020 - 7:48 am UTC

Category: SQL - Version: 12C

Viewed 100+ times

You Asked

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
140

CCC 777
DDD 470
EEE 140
BBB 600
AAA 100

expected result is:
DES SUM(AMT)
PER_NER 1847
BIZ_NER 140

CCC 777
DDD 470
EEE 140
BBB 600
AAA 100

and we said...

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.

and you rated our response

  (1 rating)

Reviews

September 13, 2020 - 10:39 am UTC

Reviewer: A reader

Thanks

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.