Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    7

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

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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')
    )
    Last edited by tonkuma; 04-23-10 at 00:58. Reason: Add test data...

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    Thank you , it worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •