6 Replies Latest reply: Feb 28, 2014 6:41 AM by Karthick_Arp RSS

    JOIN vs Union vs ??

    8ccd7461-5c47-4d50-aa66-57eaa0000cff

      NEED TO FORMAT QUERY TO CAPTURE ROWS BASED ON 3 DIFF DATES FROM 3 DIFF TABLES

      TABLE 1 setup, etc
      item#, sold date, po#

      111, 1/1/14, poabc

      222, 1/2/14, poxyz

      888, 1/9/14,polmn

      TABLE2 processed

      item#, process date, user, etc

      111, 1/12/14, ABC123

      333, 1/3/14, 345XYA

      TABLE3 cancelled

      Item#, cancel date, reason

      222,1/4/14, reason 1

      555, 1/8/14, reason 2

       

      output should be formatted as item#, sold date, process date, cancelled date, etc,, and include what dates are available

        • 1. Re: JOIN vs Union vs ??
          AnnPricks E

          Please provide CREATE table and insert script for above three table and post expected result

          • 3. Re: JOIN vs Union vs ??
            Karthick_Arp

            Like this?

             

            SQL> with table1
              2  as
              3  (
              4  select 111 item#, to_date('1/01/14', 'dd/mm/rr') sold_dt, 'poabc' po# from dual
              5  union all
              6  select 222, to_date('1/02/14', 'dd/mm/rr'), 'poxyz' from dual
              7  union all
              8  select 888, to_date('1/09/14', 'dd/mm/rr'), 'polmn' from dual
              9  ), table2
            10  as
            11  (
            12  select 111 item#, to_date('1/12/14', 'dd/mm/rr') process_dt, 'ABC123' usr from dual
            13  union all
            14  select 333, to_date('1/03/14', 'dd/mm/rr'), '345XYA' from dual
            15  ), table3
            16  as
            17  (
            18  select 222 item#, to_date('1/04/14', 'dd/mm/rr') cancel_dt, 'reason 1' reason from dual
            19  union all
            20  select 555, to_date('1/08/14', 'dd/mm/rr'), 'reason 2' from dual
            21  )
            22  select nvl(t.item#, t3.item#) item#
            23       , t.sold_dt
            24       , t.process_dt
            25       , t3.cancel_dt
            26       , t.po#
            27       , t.usr
            28       , t3.reason
            29    from (
            30            select nvl(t1.item#, t2.item#) item#
            31                 , t1.sold_dt
            32                 , t2.process_dt
            33                 , t1.po#
            34                 , t2.usr
            35              from t1.table1 t1
            36              full join table2 t2
            37                on t1.item# = t2.item#
            38         ) t
            39    full join table3 t3
            40      on t.item# = t3.item#
            41   order
            42      by 1
            43  /

             

                 ITEM# SOLD_DT   PROCESS_D CANCEL_DT PO#   USR    REASON
            ---------- --------- --------- --------- ----- ------ --------
                   111 01-JAN-14 01-DEC-14           poabc ABC123
                   222 01-FEB-14           01-APR-14 poxyz        reason 1
                   333           01-MAR-14                 345XYA
                   555                     01-AUG-14              reason 2
                   888 01-SEP-14                     polmn

             

            SQL>

            • 4. Re: JOIN vs Union vs ??
              AnnPricks E

              Thanks karthik for providing data.. We can use COALESCE function right?

              SELECT COALESCE(t1.item#, t2.item#,t3.item#) item# ,

                       t1.sold_dt ,

                       t2.process_dt ,

                       t3.cancel_dt ,

                       t1.po# ,

                       t2.usr,

                       t3.reason

                FROM table1 t1

                FULL JOIN table2 t2

                ON t1.item# = t2.item#

                FULL OUTER JOIN table3 t3

                ON t1.item# = t3.item#

                ORDER BY 1;


              OUTPUT:-

              ------

              ITEM# SOLD_DT   PROCESS_D CANCEL_DT PO#   USR REASON

              ---------- --------- --------- --------- ----- ------ --------

                 111 01-JAN-14 01-DEC-14       poabc ABC123
                 222 01-FEB-14       01-APR-14 poxyz    reason 1
                 333       01-MAR-14             345XYA
                 555                 01-AUG-14          reason 2
                 888 01-SEP-14                 polmn
              • 5. Re: JOIN vs Union vs ??
                Karthick_Arp

                AnnPricks E wrote:

                 

                Thanks karthik for providing data.. We can use COALESCE function right?

                 

                You can use COALESCE but you cant use FULL JOIN like that. I have twisted the data a little. Check the result.

                 

                Your query.

                 

                SQL> with table1
                  2  as
                  3  (
                  4  select 111 item#, to_date('1/01/14', 'dd/mm/rr') sold_dt, 'poabc' po# from dual
                  5  union all
                  6  select 888, to_date('1/09/14', 'dd/mm/rr'), 'polmn' from dual
                  7  ), table2
                  8  as
                  9  (
                10  select 111 item#, to_date('1/12/14', 'dd/mm/rr') process_dt, 'ABC123' usr from dual
                11  union all
                12  select 222, to_date('1/02/14', 'dd/mm/rr'), 'XYZ' from dual
                13  union all
                14  select 333, to_date('1/03/14', 'dd/mm/rr'), '345XYA' from dual
                15  ), table3
                16  as
                17  (
                18  select 222 item#, to_date('1/04/14', 'dd/mm/rr') cancel_dt, 'reason 1' reason from dual
                19  union all
                20  select 555, to_date('1/08/14', 'dd/mm/rr'), 'reason 2' from dual
                21  )
                22  SELECT COALESCE(t1.item#, t2.item#,t3.item#) item# ,
                23           t1.sold_dt ,
                24           t2.process_dt ,
                25           t3.cancel_dt ,
                26           t1.po# ,
                27           t2.usr,
                28           t3.reason
                29    FROM table1 t1
                30    FULL JOIN table2 t2
                31    ON t1.item# = t2.item#
                32    FULL OUTER JOIN table3 t3
                33    ON t1.item# = t3.item#
                34    ORDER BY 1;

                 

                     ITEM# SOLD_DT   PROCESS_D CANCEL_DT PO#   USR    REASON
                ---------- --------- --------- --------- ----- ------ --------
                       111 01-JAN-14 01-DEC-14           poabc ABC123
                       222           01-FEB-14                 XYZ
                       222                     01-APR-14              reason 1
                       333           01-MAR-14                 345XYA
                       555                     01-AUG-14              reason 2
                       888 01-SEP-14                     polmn

                 

                6 rows selected.

                 

                My Query

                 

                SQL> with table1
                  2  as
                  3  (
                  4  select 111 item#, to_date('1/01/14', 'dd/mm/rr') sold_dt, 'poabc' po# from dual
                  5  union all
                  6  select 888, to_date('1/09/14', 'dd/mm/rr'), 'polmn' from dual
                  7  ), table2
                  8  as
                  9  (
                10  select 111 item#, to_date('1/12/14', 'dd/mm/rr') process_dt, 'ABC123' usr from dual
                11  union all
                12  select 222, to_date('1/02/14', 'dd/mm/rr'), 'XYZ' from dual
                13  union all
                14  select 333, to_date('1/03/14', 'dd/mm/rr'), '345XYA' from dual
                15  ), table3
                16  as
                17  (
                18  select 222 item#, to_date('1/04/14', 'dd/mm/rr') cancel_dt, 'reason 1' reason from dual
                19  union all
                20  select 555, to_date('1/08/14', 'dd/mm/rr'), 'reason 2' from dual
                21  )
                22  select nvl(t.item#, t3.item#) item#
                23        , t.sold_dt
                24        , t.process_dt
                25        , t3.cancel_dt
                26        , t.po#
                27        , t.usr
                28        , t3.reason
                29     from (
                30             select nvl(t1.item#, t2.item#) item#
                31                  , t1.sold_dt
                32                  , t2.process_dt
                33                  , t1.po#
                34                  , t2.usr
                35               from t1.table1 t1
                36               full join table2 t2
                37                 on t1.item# = t2.item#
                38          ) t
                39     full join table3 t3
                40       on t.item# = t3.item#
                41    order
                42       by 1;

                 

                     ITEM# SOLD_DT   PROCESS_D CANCEL_DT PO#   USR    REASON
                ---------- --------- --------- --------- ----- ------ --------
                       111 01-JAN-14 01-DEC-14           poabc ABC123
                       222           01-FEB-14 01-APR-14       XYZ    reason 1
                       333           01-MAR-14                 345XYA
                       555                     01-AUG-14              reason 2
                       888 01-SEP-14                     polmn

                 

                SQL>

                • 6. Re: JOIN vs Union vs ??
                  AnnPricks E

                  Yes fine karthik.. Thanks for the info..