CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

Oracle Query Not Returning All rows from both subqueries

I have two subqueries that are essentially querying for the same thing minus a dimension (the flag column). I am then grabbing data from those two subqueries and returning them in an 'outer query'. (With the Orig and RE Values (VAL) in the same row)

The Composite Key I am joining on is composed of the following columns:

P_NAME, REAL_DATE, UTC_TIME, DATA_TYPE & LOCAL_TS

Some Sample Data Returned from First Subquery

P_NAME    REAL_DATE   UTC_TIME    DATA_TYPE   VAL   LOCAL_TS      FLAG
John      01-01-2018  01:00:00AM  Height      60    04:00:00AM    ORIG
Jacob     01-01-2018  02:00:00AM  Height      68    05:00:00AM    ORIG
Jingle    01-01-2018  01:00:00AM  Height      65    04:00:00AM    ORIG
Heimer    01-01-2018  04:00:00AM  Height      68    07:00:00AM    ORIG
Schmidt   01-01-2018  05:00:00AM  Height      72    08:00:00AM    ORIG

Some Sample Data Returned from the Second Subquery

P_NAME    REAL_DATE   UTC_TIME    DATA_TYPE   VAL   LOCAL_TS      FLAG
John      01-01-2018  01:00:00AM  Height      90    04:00:00AM    RE
Jacob     01-01-2018  02:00:00AM  Height      98    05:00:00AM    RE
Jingle    01-01-2018  01:00:00AM  Height      95    04:00:00AM    RE

What I need returned from the first and second subqueries JOINED

P_NAME    REAL_DATE   UTC_TIME    DATA_TYPE   O_VAL  RE_VAL    LOCAL_TS
John      01-01-2018  01:00:00AM  Height      60       90      04:00:00AM
Jacob     01-01-2018  02:00:00AM  Height      68       98      05:00:00AM
Jingle    01-01-2018  01:00:00AM  Height      65       95      04:00:00AM
Heimer    01-01-2018  04:00:00AM  Height      68      null     07:00:00AM
Schmidt   01-01-2018  05:00:00AM  Height      72      null     08:00:00AM

What I've tried but doesn't work:

select O.O_P_NAME as P_NAME
     , O.O_REAL_DATE as REAL_DATE
     , O.O_UTC_TIME as UTC_TIME
     , O.O_DATA_TYPE as DATA_TYPE
     , CASE WHEN O.O_VAL = R.R_VAL
            THEN null
            ELSE R.R_VAL
             END     as RESUBMITTED_VAL
     , O.O_VAL       as ORIG_VAL
     , O.O_LOCAL_TS  as LOCAL_TS
    FROM(
        (SELECT P_NAME as O_P_NAME
              , REAL_DATE as O_REAL_DATE
              , UTC_TIME as O_UTC_TIME
              , DATA_TYPE as O_DATA_TYPE
              , VAL as O_VAL
              , LOCAL_TS as O_LOCAL_TS
              , FLAG as O_FLAG
           FROM TABLE
          WHERE FLAG = 'ORIG' 
        ) O
        JOIN
        (SELECT P_NAME as R_P_NAME
              , REAL_DATE as R_REAL_DATE
              , UTC_TIME as R_UTC_TIME
              , DATA_TYPE as R_DATA_TYPE
              , VAL as R_VAL
              , LOCAL_TS as R_LOCAL_TS
              , FLAG as R_FLAG
           FROM TABLE
          WHERE FLAG = 'RE' 
        ) R
        ON O.O_P_NAME = R.R_P_NAME
       AND O.O_REAL_DATE = R.R_REAL_DATE
       AND O.O_UTC_TIME = R.R_UTC_TIME
       AND O.O_DATA_TYPE = R.R_DATA_TYPE
       AND O.O_LOCAL_TS = R.R_LOCAL_TS
        );

Upvotes: 0

Views: 79

Answers (1)

Paul X
Paul X

Reputation: 379

You don't need the two subqueries for this. Just do a case statement to separate the fields.

SELECT P_NAME 
              , REAL_DATE 
              , UTC_TIME 
              , DATA_TYPE 
              , MAX(CASE WHEN FLAG='ORIG' THEN VAL END) as O_VAL
              , MAX(CASE WHEN FLAG='RE' THEN VAL END) as R_VAL
              , LOCAL_TS 
           FROM TABLE
GROUP BY P_NAME, REAL_DATE, UTC_TIME, DATA_TYPE, LOCAL_TS

Bonus answer: if you really decided you needed the subqueries, use a UNION instead of a join because you don't know which table would have the extra values.

Upvotes: 1

Related Questions