How to combine the result set from UNION with another table.

DB2begin · #1 (**permalink**) 04-22-10, 22:49

The data is stored in 3 different tables.I want the result set , UNION of table HSTRY_A and HSTRY_B .Then combine the result set of TYPE_CD_VALUE. The search criteria is to get the data between STRT-DT 2010-04-01 AND 2010-04-07.

TABLE-NM: TYPE_CD_VALUE

Code:

STRT-DT       END-DT        TYPE-CD    TYPE-VALUE    
2010-04-01     NULL             119           A1             
2010-04-02    2010-04-03    115            B1              
2010-04-06     NULL            221            D1

TABLE-NM : HSTRY_A

Code:

STRT-DT      GRP-TYPE       GRP-VALUE   
2010-04-01     E12             1079
2010-04-02     B13             7090
2010-04-04     D87             9050
2010-04-07     F21             7872

TABLE-NM : HSTRY_B

Code:

STRT-DT      GRP-TYPE       GRP-VALUE   

2010-04-01     E12             1079
2010-04-03     C14             2109
2010-04-05     E27             5190
2010-04-07     G21             6862

The following is how i want the result set , in 1 table with data from all the 3 above table. Appreciate if some one can help me with a DB2 SQL or let me know whether this is possible.

HTML Code:

STRT-DT     END-DT  TYPE-CD   TYPE-VALUE  GRP_TYP      GRP_VALUE
2010-04-01   NULL      119       A1          E12         1079
2010-04-02  2010-04-03 115       B1          B13         7090
2010-04-03  ----                              C14        2109
2010-04-04  ----            ----    ---       D87       9050
2010-04-05  ---           ----     ---        E27       5190
2010-04-06  NULL        221     D1          ---         ---
2010-04-07  ---          -----    ---         F21      7872
2010-04-07  ----         ----     ---         G21      6862

tonkuma · #2 (**permalink**) 04-22-10, 23:50

For example...

Code:

SELECT COALESCE(t.STRT_DT , h.STRT_DT) AS STRT_DT
     , END_DT
     , TYPE_CD  , TYPE_VALUE
     , GRP_TYPE , GRP_VALUE
  FROM (SELECT *
          FROM TYPE_CD_VALUE
         WHERE STRT_DT BETWEEN '2010-04-01' AND '2010-04-07'
       ) t
  FULL OUTER JOIN
       (SELECT *
          FROM HSTRY_A
         WHERE STRT_DT BETWEEN '2010-04-01' AND '2010-04-07'
        UNION
        SELECT *
          FROM HSTRY_B
         WHERE STRT_DT BETWEEN '2010-04-01' AND '2010-04-07'
       ) h
   ON  h.STRT_DT = t.STRT_DT
 ORDER BY
       STRT_DT
     , GRP_TYPE
;
------------------------------------------------------------------------------

STRT_DT    END_DT     TYPE_CD TYPE_VALUE GRP_TYPE GRP_VALUE
---------- ---------- ------- ---------- -------- ---------
2010-04-01 NULL       119     A1         E12      1079     
2010-04-02 2010-04-03 115     B1         B13      7090     
2010-04-03 -          -       -          C14      2109     
2010-04-04 -          -       -          D87      9050     
2010-04-05 -          -       -          E27      5190     
2010-04-06 NULL       221     D1         -        -        
2010-04-07 -          -       -          F21      7872     
2010-04-07 -          -       -          G21      6862     

  8 record(s) selected.

Test data was...

Code:

WITH
/**************************************************
**********   Start of sample data        **********
**************************************************/
TYPE_CD_VALUE
(STRT_DT , END_DT , TYPE_CD , TYPE_VALUE) AS (
VALUES
  ('2010-04-01' , 'NULL'       , '119' , 'A1')             
, ('2010-04-02' , '2010-04-03' , '115' , 'B1')
, ('2010-04-06' , 'NULL'       , '221' , 'D1')
)
,HSTRY_A
(STRT_DT , GRP_TYPE , GRP_VALUE) AS (
VALUES
  ('2010-04-01' , 'E12' , '1079')
, ('2010-04-02' , 'B13' , '7090')
, ('2010-04-04' , 'D87' , '9050')
, ('2010-04-07' , 'F21' , '7872')
)
,HSTRY_B
(STRT_DT , GRP_TYPE , GRP_VALUE) AS (
VALUES
  ('2010-04-01' , 'E12' , '1079')
, ('2010-04-03' , 'C14' , '2109')
, ('2010-04-05' , 'E27' , '5190')
, ('2010-04-07' , 'G21' , '6862')
)

DB2begin · #3 (**permalink**) 05-10-10, 22:29

Thank you , it worked.

Internet Services

Web Development

Digital Marketing

Consumer Tech