Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

SQL

User_LCHKLOct 4 2019 — edited Oct 5 2019

I need to differentiate the data from Query1 and query2 for reporting purpose:

Only difference is case statement between  both queries.

Present approach:-

Currently I am using union operator and using flag concept to differentiate both the queries.

Is there any way where we can write in single sql statement and I am fine with one more static table also.

Query1:‪

One view of data:-

select‬‎ ‪

year_qtr,

‬‎ ‪ CASE

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪80'‬‎,‪‬‎'‪A8'‬‎,‪‬‎'‪R8'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G1'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪D5'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G2'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪A5'‬‎,‪‬‎'‪K8'‬‎,‪‬‎'‪K9'‬‎,‪‬‎'‪TQ'‬‎,‪‬‎'‪RQ'‬‎,‪‬‎'‪R9'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G3'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎(‪‬‎'‪H5'‬‎,‪‬‎'‪H8'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G4'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪Z8'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G5'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪45'‬‎,‪‬‎'‪63'‬‎,‪‬‎'‪7H'‬‎,‪‬‎'‪Z1'‬‎,‪‬‎'‪7Y'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G6'

‬‎ ‪   END‬‎ ‪AS‬‎ ‪BRAND‬‎,‪

    round‬‎(‪sum‬‎(‪USD_PLAN_GL_POSTD_AMT‬‎)‪‬‎ ‪‬‎*‪‬‎ ‪‬‎.‪000001‬‎,‪6‬‎)‪as‬‎ ‪USD_PLAN_GL_POSTD_AMT‬‎ ‪

‬‎ ‪   from‬‎ ‪  table1 ‪rev

   group by

  ‪year_qtr,

‬‎ ‪  CASE

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪80'‬‎,‪‬‎'‪A8'‬‎,‪‬‎'‪R8'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G1'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪D5'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G2'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪A5'‬‎,‪‬‎'‪K8'‬‎,‪‬‎'‪K9'‬‎,‪‬‎'‪TQ'‬‎,‪‬‎'‪RQ'‬‎,‪‬‎'‪R9'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G3'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎(‪‬‎'‪H5'‬‎,‪‬‎'‪H8'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G4'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪Z8'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G5'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪45'‬‎,‪‬‎'‪63'‬‎,‪‬‎'‪7H'‬‎,‪‬‎'‪Z1'‬‎,‪‬‎'‪7Y'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G6'

‬‎ ‪   END‬‎‪

Query2:‪

Another view of data:-

select‬‎ ‪

year_qtr,

‬‎ ‪ CASE

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪80'‬‎,‪‬‎'‪A8'‬‎,‪‬‎'‪R8'‬‎,'R1')‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G1'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ in('‪D6','D7')

‬‎ ‪     THEN‬‎ ‪‬‎'‪G2'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪A5'‬‎,‪‬‎'‪K8'‬‎,‪‬‎'‪K9'‬‎,‪‬‎'‪TQ'‬‎,‪‬‎'‪RQ'‬‎,‪‬‎'‪R8','R7'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G3'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎(‪‬‎'‪H3'‬‎,‪‬‎'‪H9'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G4'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪Z8'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G5'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪45'‬‎,‪‬‎'‪63'‬‎,‪‬‎'‪7I'‬‎,‪‬‎'‪Z6'‬‎‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G6'

‬‎ ‪   END‬‎ ‪AS‬‎ ‪BRAND‬‎,‪

    round‬‎(‪sum‬‎(‪USD_PLAN_GL_POSTD_AMT‬‎)‪‬‎ ‪‬‎*‪‬‎ ‪‬‎.‪000001‬‎,‪6‬‎)‪as‬‎ ‪USD_PLAN_GL_POSTD_AMT‬‎ ‪

‬‎ ‪   from‬‎ ‪  table1 ‪rev

   group by

  ‪year_qtr,

‬‎ ‪ CASE

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪80'‬‎,‪‬‎'‪A8'‬‎,‪‬‎'‪R8'‬‎,'R1')‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G1'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ in('‪D6','D7')

‬‎ ‪     THEN‬‎ ‪‬‎'‪G2'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪A5'‬‎,‪‬‎'‪K8'‬‎,‪‬‎'‪K9'‬‎,‪‬‎'‪TQ'‬‎,‪‬‎'‪RQ'‬‎,‪‬‎'‪R8','R7'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G3'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎(‪‬‎'‪H3'‬‎,‪‬‎'‪H9'‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G4'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎=‪‬‎'‪Z8'

‬‎ ‪     THEN‬‎ ‪‬‎'‪G5'

‬‎ ‪     WHEN‬‎ ‪REV‬‎.‪DIVISION‬‎ ‪IN‬‎ ‪‬‎(‪‬‎'‪45'‬‎,‪‬‎'‪63'‬‎,‪‬‎'‪7I'‬‎,‪‬‎'‪Z6'‬‎‬‎)‪

‬‎ ‪     THEN‬‎ ‪‬‎'‪G6'

‬‎ ‪   END‬‎‪

NOTE1:-I have totally 4 different sql queries where there is different in case statement and for understanding  purpose I gave 2 sql statements

Comments
Post Details
Added on Oct 4 2019
12 comments
383 views