Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: can we use Merge and With Together

    This is wat i am trying :

    Tab1
    Item Col1 Col2 Col3
    Abc x y z
    Cde p q r

    Tab2
    Col1 Col2
    AA x
    BB y
    CC z

    with fs as ( Select * from Table1)
    select tab1.Item, Tab2.Col2, case
    when tab2.Col1 = 'AA' then fs.Col1
    when tab2.Col2 = 'BB' then fs.Col2
    else fs.col3 end NewCol
    from Tab2, fs

    I get this result


    Item, col2, NewCol
    Abc AA x
    Abc BB y
    Abc CC z
    Cde AA p
    Cde BB q
    Cde CC r

    that converted Horrizontal data to vertical

    Tab4
    Item, col2, NewCol
    Abc AA xasd
    Abc QQ 121
    Cde CC 888

    Now i want merge Vertical data into Tab4

    Merge into tab4 Using ( above resul) tm on tm.item = tab4.item
    when matched ...
    when Not matched ...


    to get final resuts as

    Item, col2, NewCol
    Abc AA x
    Abc BB y
    Abc CC z
    Abc QQ 121
    Cde AA p
    Cde BB q
    Cde CC r


    The WITH command does not work merge


    or should i use union

    Merge into tab4 Using (
    select style, tab1.col1 as newcol from Tab1
    union
    select style, tab1.col2 as newcol from Tab1


    ) tm on tm.item = tab4.item
    when matched ...
    when Not matched ...


    Thanks for you time

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your following query has syntax erros and doutful clauses.
    And I couldn't see any reason of neccesity of CTE.

    with fs as ( Select * from Table1)
    select tab1.Item, Tab2.Col2, case
    when tab2.Col1 = 'AA' then fs.Col1
    when tab2.Col2 = 'BB' then fs.Col2
    else fs.col3 end NewCol
    from Tab2, fs
    So, I'll ignore it.

    Anyway, your required result could get by this way.

    Make table and data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE Tab1
    (Item CHAR(3)
    ,Col1 CHAR(3)
    ,Col2 CHAR(3)
    ,Col3 CHAR(3)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Tab1
    VALUES
     ('Abc', 'x', 'y', 'z')
    ,('Cde', 'p', 'q', 'r');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE Tab2
    (Col1 CHAR(3)
    ,Col2 CHAR(3)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Tab2
    VALUES
     ('AA', 'x')
    ,('BB', 'y')
    ,('CC', 'z');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE Tab4 
    (Item   CHAR(3)
    ,col2   CHAR(3)
    ,NewCol CHAR(5)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Tab4
    VALUES
     ('Abc', 'AA', 'xasd')
    ,('Abc', 'QQ', '121')
    ,('Cde', 'CC', '888');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    convert Horrizontal data to vertical:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT t1.item
         , t2.col1 AS col2
         , CASE t2.col1
           WHEN 'AA' THEN t1.col1
           WHEN 'BB' THEN t1.col2
           ELSE           t1.col3
           END  AS newcol
      FROM tab1 t1
         , tab2 t2;
    ------------------------------------------------------------------------------
    
    ITEM COL2 NEWCOL
    ---- ---- ------
    Abc  AA   x     
    Abc  BB   y     
    Abc  CC   z     
    Cde  AA   p     
    Cde  BB   q     
    Cde  CC   r     
    
      6 record(s) selected.
    MERGE statement:
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO Tab4 t4
    USING (SELECT t1.item
                , t2.col1 AS col2
                , CASE t2.col1
                  WHEN 'AA' THEN t1.col1
                  WHEN 'BB' THEN t1.col2
                  ELSE           t1.col3
                  END  AS newcol
             FROM tab1 t1
                , tab2 t2
          ) r   -- converted Horrizontal data to vertical
      ON  r.item = t4.item
      AND r.col2 = t4.col2
    WHEN MATCHED
    THEN UPDATE
         SET newcol = r.newcol
    WHEN NOT MATCHED
    THEN INSERT
         VALUES (r.item, r.col2, r.newcol)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Result Tab4:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
      FROM Tab4
     ORDER BY
           item
         , col2;
    ------------------------------------------------------------------------------
    
    ITEM COL2 NEWCOL
    ---- ---- ------
    Abc  AA   x     
    Abc  BB   y     
    Abc  CC   z     
    Abc  QQ   121   
    Cde  AA   p     
    Cde  BB   q     
    Cde  CC   r     
    
      7 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    can we use Merge and With Together
    No,
    Syntax of MERGE doesn't include CTE.

    Or, Yes,
    MERGE ... WITH RR | RS | CS | UR
    But, this must be not your requested answer.

    Please see "DB2 SQL Reference".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 Version 9.1 for z/OS,
    following statement may be possible.
    But, I can't test it, because I have no access to such enviromnet.

    WITH cte AS (...)
    SELECT ...
    FROM FINAL TABLE(MERGE statement)

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    Guys,

    My problem is in the same line.. the only difference is i cannot use a join as my logic is quite complicate. so, i have to use "with " statement and also i want to use the merge statement. Can anyone tell me how to do so.. and also my DB2 version is 8 not 9

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that MERGE with common-table-expression is supported on DB2 version 9 for z/OS only, now.

    But, it may be possible to UPDATE/INSERT in a statement with common-table-expressions, at least on DB2 8.2 or later for LUW.

    Here is an example tested on DB2 9.7 for Windows.

    Prepare data(of Tab4):
    Code:
    ------------------------------ Commands Entered ------------------------------
    DELETE FROM tab4;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Tab4
    VALUES
     ('Abc', 'AA', 'xasd')
    ,('Abc', 'QQ', '121')
    ,('Cde', 'CC', '888');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    UPDATE/INSERT
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH vertical AS (
    SELECT t1.item
         , t2.col1 AS col2
         , CASE t2.col1
           WHEN 'AA' THEN t1.col1
           WHEN 'BB' THEN t1.col2
           ELSE           t1.col3
           END  AS newcol
      FROM tab1 t1
         , tab2 t2
    )
    ,matched AS (
    SELECT COUNT(*) AS updated_rows
      FROM FINAL TABLE
           (UPDATE tab4 t4
               SET newcol
                 = (SELECT newcol
                      FROM vertical r
                     WHERE r.item = t4.item
                       AND r.col2 = t4.col2
                   )
             WHERE EXISTS
                   (SELECT newcol
                      FROM vertical r
                     WHERE r.item = t4.item
                       AND r.col2 = t4.col2
                   )
           ) updt
    )
    ,not_matched AS (
    SELECT COUNT(*) AS inserted_rows
      FROM FINAL TABLE
           (INSERT INTO tab4
            SELECT r.item, r.col2, r.newcol
              FROM vertical r
             WHERE NOT EXISTS
                   (SELECT *
                      FROM tab4 t4
                     WHERE r.item = t4.item
                       AND r.col2 = t4.col2
                   )
           ) isrt
    )
    SELECT *
      FROM matched
         , not_matched;
    ------------------------------------------------------------------------------
    
    UPDATED_ROWS INSERTED_ROWS
    ------------ -------------
               2             4
    
      1 record(s) selected.
    Result of Tab4:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Tab4
     ORDER BY item, col2;
    ------------------------------------------------------------------------------
    
    ITEM COL2 NEWCOL
    ---- ---- ------
    Abc  AA   x     
    Abc  BB   y     
    Abc  CC   z     
    Abc  QQ   121   
    Cde  AA   p     
    Cde  BB   q     
    Cde  CC   r     
    
      7 record(s) selected.

Posting Permissions

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