Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Unanswered: Issue with MERGE Statement when ROW_NUMBER() is used

    Hi,

    Would appreciate if anyone can help in figuring out why the ROW_NUMBER() doesn't generate the next sequence.

    Sample:
    Code:
    DROP TABLE emp;
    
    CREATE TABLE emp (
        empid        INTEGER NOT NULL PRIMARY KEY,
        username  VARCHAR(100),
        firstname VARCHAR(100),
        lastname  VARCHAR(100)
    );
    
    CREATE INDEX ui_emp1 ON emp 
    (
        username ASC
    );
    
    INSERT INTO emp (empid, username, firstname, lastname)
        SELECT 1, 'user1', 'firstname1', 'lastname1' FROM sysibm.sysdummy1 UNION ALL
        SELECT 2, 'user2', 'firstname2', 'lastname2' FROM sysibm.sysdummy1 UNION ALL
        SELECT 3, 'user3', 'firstname3', 'lastname3' FROM sysibm.sysdummy1 UNION ALL
        SELECT 4, 'user4', 'firstname4', 'lastname4' FROM sysibm.sysdummy1 UNION ALL
        SELECT 5, 'user5', 'firstname5', 'lastname5' FROM sysibm.sysdummy1
        ORDER BY 1;

    The following works:
    Code:
    MERGE INTO emp
    USING
        (
            SELECT 'user6' username, 'firstname6' firstname, 'lastname6' lastname FROM sysibm.sysdummy1
        ) AS t
    ON
        emp.username    = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
            VALUES ((SELECT MAX(empid) FROM emp) + ROW_NUMBER() OVER (), t.username, t.firstname, t.lastname)
    ELSE IGNORE;
    However, the following doesn't work when trying to update multiple rows:
    Code:
    MERGE INTO emp
    USING
        (
            SELECT 'user4' username, 'firstname4' firstname, 'lastname4' lastname FROM sysibm.sysdummy1 UNION ALL
            SELECT 'user5' username, 'firstname5' firstname, 'lastname5' lastname FROM sysibm.sysdummy1 UNION ALL
            SELECT 'user6' username, 'firstname6' firstname, 'lastname6' lastname FROM sysibm.sysdummy1 UNION ALL
            SELECT 'user7' username, 'firstname7' firstname, 'lastname7' lastname FROM sysibm.sysdummy1
        ) AS t
    ON
        emp.username    = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
            VALUES ((SELECT MAX(empid) FROM emp) + ROW_NUMBER() OVER (), t.username, t.firstname, t.lastname)
    ELSE IGNORE;
    I tried to use PARTITION BY and/or ORDER BY clause in the ROW_NUMBER() OVER (), but no success.

    Note: DB2 Version: 8.2.3



    Thanks in advance.


    Regards,
    Rohit

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "doesn't work" is not a good description of issues.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Consider subject rows of "ROW_NUMBER() OVER ()".

    You are using it in a row to insert.

  4. #4
    Join Date
    Nov 2011
    Posts
    20
    Thanks for pointing out the english phrase "tonkuma", but I was in an hurry to get a solution and also admit that I'm not too good in english phrases.

    I've gone through your postings and also you helped in one of my past queries. So, can I get expect some solution to my problem?


    Thanks!

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    db2 => MERGE INTO emp
    db2 (cont.) => USING
    db2 (cont.) =>     (
    db2 (cont.) =>         SELECT 'user6' username, 'firstname6' firstname, 'lastname6' lastname FROM sysibm.sysdummy1
    db2 (cont.) =>     ) AS t
    db2 (cont.) => ON
    db2 (cont.) =>     emp.username    = t.username
    db2 (cont.) => WHEN NOT MATCHED THEN
    db2 (cont.) => 
    db2 (cont.) =>      INSERT (empid, username, firstname, lastname)
    db2 (cont.) =>         VALUES ((SELECT MAX(empid) FROM emp) + ROW_NUMBER() OVER (), t.username, t.firstname, t.lastname)
    db2 (cont.) => ELSE IGNORE;
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0120N  Invalid use of an aggregate function or OLAP function.  
    SQLSTATE=42903
    What makes you think that the above "works"?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    I don't have access to 8.2 so I can't verify, but I would try moving max aggregate to the USING clause:

    Code:
    MERGE INTO emp
    USING (
            
            SELECT MAX(empid) + ROW_NUMBER() OVER () as empid
                        , x.username, x.firstname, x.lastname
            FROM emp
            CROSS JOIN 
                 LATERAL(VALUES ('user4', 'firstname4', 'lastname4')
                                             , ('user5', 'firstname5', 'lastname5') ) x(username,firstname,lastname) 
      
        ) AS t
    ON emp.username = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
         VALUES (t.empid, t.username, t.firstname, t.lastname)
    Completely untested
    --
    Lennart

  7. #7
    Join Date
    Nov 2011
    Posts
    20
    When executing this, I'm getting the following error;
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token ") t" was found following ",firstname,
    lastname)".  Expected tokens may include:  "<space>".  SQLSTATE=42601
    
    SQL0104N  An unexpected token ") t" was found following ",firstname,lastname)".  Expected tokens may include:  "<space>                                      ".
    A further help would be appreciated.

  8. #8
    Join Date
    Nov 2011
    Posts
    20
    When checked again, the sub-query
    Code:
    SELECT MAX(empid) + ROW_NUMBER() OVER () as empid
                        , x.username, x.firstname, x.lastname
            FROM emp
            CROSS JOIN 
                 LATERAL(VALUES ('user4', 'firstname4', 'lastname4')
                                             , ('user5', 'firstname5', 'lastname5') ) x(username,firstname,lastname)
    is throwing up following error:
    Code:
    SQL0104N  An unexpected token "END-OF-STATEMENT" was found following ",
    firstname,lastname)".  Expected tokens may include:  "JOIN <joined_table>".  
    SQLSTATE=42601
    
    SQL0104N  An unexpected token "END-OF-STATEMENT" was found following ",firstname,lastname)".  Expected tokens may include:  "JOIN <joined_table>             ".

    Hope this helps.

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Rohit Oberoi View Post
    When checked again, the sub-query
    Code:
    SELECT MAX(empid) + ROW_NUMBER() OVER () as empid
                        , x.username, x.firstname, x.lastname
            FROM emp
            CROSS JOIN 
                 LATERAL(VALUES ('user4', 'firstname4', 'lastname4')
                                             , ('user5', 'firstname5', 'lastname5') ) x(username,firstname,lastname)
    is throwing up following error:
    Code:
    SQL0104N  An unexpected token "END-OF-STATEMENT" was found following ",
    firstname,lastname)".  Expected tokens may include:  "JOIN <joined_table>".  
    SQLSTATE=42601
    
    SQL0104N  An unexpected token "END-OF-STATEMENT" was found following ",firstname,lastname)".  Expected tokens may include:  "JOIN <joined_table>             ".

    Hope this helps.

    I tested my query against DB2 v10.1.0.0", "s120403", "LINUXIA32101" Fix Pack "0" and it appears as if a GROUP BY clause is needed. Also, you [may] need to name the columns in the using clause as in:

    Code:
    MERGE INTO emp
    USING (
            
            SELECT MAX(empid) + ROW_NUMBER() OVER () as empid
                        , x.username, x.firstname, x.lastname
            FROM emp
            CROSS JOIN 
                 LATERAL(VALUES ('user4', 'firstname4', 'lastname4')
                              , ('user5', 'firstname5', 'lastname5') ) x(username,firstname,lastname) 
            GROUP BY x.username, x.firstname, x.lastname 
        ) AS t (empid, username, firstname, lastname)
    ON emp.username = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
         VALUES (t.empid, t.username, t.firstname, t.lastname);
    Whether that will work on v8 I don't know.
    --
    Lennart

  10. #10
    Join Date
    Nov 2011
    Posts
    20
    When I execute the updated solution using GROUP-BY, I get below exception:

    Code:
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "CROSS JOIN LATERAL(VALUES ('user4', '" was 
    found following "x.lastname FROM emp,".  Expected tokens may include:  
    "<table_ref>".  SQLSTATE=42601
    
    SQL0104N  An unexpected token "CROSS JOIN LATERAL(VALUES ('user4', '" was found following "x.lastname FROM emp,".  Expected tokens may include:  "<table_ref>".
    Not sure whether DB2 v8.x supports CROSS JOIN, so executed the same by commenting out the CROSS JOIN and it worked.

    Code:
    MERGE INTO emp
    USING (
            SELECT MAX(empid) + ROW_NUMBER() OVER () as empid
                        , x.username, x.firstname, x.lastname
            FROM emp,
    --        CROSS JOIN 
                 LATERAL(VALUES ('user4', 'firstname4', 'lastname4')
                              , ('user6', 'firstname6', 'lastname6')
                              , ('user5', 'firstname5', 'lastname5') ) x(username,firstname,lastname) 
            GROUP BY x.username, x.firstname, x.lastname 
        ) AS t (empid, username, firstname, lastname)
    ON emp.username = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
         VALUES (t.empid, t.username, t.firstname, t.lastname);

    But, I'm getting my old issue again. Here is the output after adding a new row which is not existing in the table. The issue in the below output is that there is a gap in the primary key sequence, which I want to avoid. The requirement is to have continued sequence.

    Code:
    EMPID  USERNAME  FIRSTNAME   LASTNAME
    -----  --------  ----------  ---------
    1      user1     firstname1  lastname1
    2      user2     firstname2  lastname2
    3      user3     firstname3  lastname3
    4      user4     firstname4  lastname4
    5      user5     firstname5  lastname5
    8      user6     firstname6  lastname6

    I don't know this output is because I removed the CROSS JOIN, or it's desired with this query.

    Any more info on having a continued sequence for the primary key column? Note: Don't prefer to use DB sequence.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think MERGE may be not necessary.

    Tested on DB2 9.7.5 on Windows/XP.

    Create table and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE emp;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE emp
    ( empid     INTEGER NOT NULL PRIMARY KEY
    , username  VARCHAR(100)
    , firstname VARCHAR(100)
    , lastname  VARCHAR(100)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO emp
    ( empid, username, firstname, lastname )
    VALUES
      ( 1 , 'user1' , 'firstname1' , 'lastname1' )
    , ( 2 , 'user2' , 'firstname2' , 'lastname2' )
    , ( 3 , 'user3' , 'firstname3' , 'lastname3' )
    , ( 4 , 'user4' , 'firstname4' , 'lastname4' )
    , ( 5 , 'user5' , 'firstname5' , 'lastname5' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    INSERT statement:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO emp
    ( username, firstname, lastname , empid )
    WITH
      t( username, firstname, lastname ) AS (
    VALUES ('user4', 'firstname4', 'lastname4')
         , ('user6', 'firstname6', 'lastname6')
         , ('user5', 'firstname5', 'lastname5')
    )
    SELECT username, firstname, lastname
         , (SELECT MAX(empid) FROM emp)
           + ROW_NUMBER() OVER( ORDER BY username )
     FROM  t
     WHERE NOT EXISTS
          (SELECT 0
            FROM  emp
            WHERE emp.username = t.username
          )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empid
         , VARCHAR(username  , 10) AS username
         , VARCHAR(firstname , 10) AS firstname
         , VARCHAR(lastname  , 10) AS lastname
     FROM  emp
     ORDER BY
           empid
    ;
    ------------------------------------------------------------------------------
    
    EMPID       USERNAME   FIRSTNAME  LASTNAME  
    ----------- ---------- ---------- ----------
              1 user1      firstname1 lastname1 
              2 user2      firstname2 lastname2 
              3 user3      firstname3 lastname3 
              4 user4      firstname4 lastname4 
              5 user5      firstname5 lastname5 
              6 user6      firstname6 lastname6 
    
      6 record(s) selected.

    Or, you may want to use IDENTITY, even if you don't prefer to use DB sequence.

    Create table and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DROP TABLE emp;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE emp
    ( empid     INTEGER NOT NULL
                GENERATED ALWAYS AS IDENTITY
    , username  VARCHAR(100)
    , firstname VARCHAR(100)
    , lastname  VARCHAR(100)
    , PRIMARY KEY( empid )
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO emp
    ( username, firstname, lastname )
    VALUES
      ( 'user1' , 'firstname1' , 'lastname1' )
    , ( 'user2' , 'firstname2' , 'lastname2' )
    , ( 'user3' , 'firstname3' , 'lastname3' )
    , ( 'user4' , 'firstname4' , 'lastname4' )
    , ( 'user5' , 'firstname5' , 'lastname5' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Initial contents:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empid
         , VARCHAR(username  , 10) AS username
         , VARCHAR(firstname , 10) AS firstname
         , VARCHAR(lastname  , 10) AS lastname
     FROM  emp
     ORDER BY
           empid
    ;
    ------------------------------------------------------------------------------
    
    EMPID       USERNAME   FIRSTNAME  LASTNAME  
    ----------- ---------- ---------- ----------
              1 user1      firstname1 lastname1 
              2 user2      firstname2 lastname2 
              3 user3      firstname3 lastname3 
              4 user4      firstname4 lastname4 
              5 user5      firstname5 lastname5 
    
      5 record(s) selected.
    INSERT statement:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO emp
    ( username, firstname, lastname )
    WITH
      t( username, firstname, lastname ) AS (
    VALUES ('user4', 'firstname4', 'lastname4')
         , ('user6', 'firstname6', 'lastname6')
         , ('user5', 'firstname5', 'lastname5')
         , ('user8', 'firstname8', 'lastname8')
    )
    SELECT username, firstname, lastname
     FROM  t
     WHERE NOT EXISTS
          (SELECT 0
            FROM  emp
            WHERE emp.username = t.username
          )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT empid
         , VARCHAR(username  , 10) AS username
         , VARCHAR(firstname , 10) AS firstname
         , VARCHAR(lastname  , 10) AS lastname
     FROM  emp
     ORDER BY
           empid
    ;
    ------------------------------------------------------------------------------
    
    EMPID       USERNAME   FIRSTNAME  LASTNAME  
    ----------- ---------- ---------- ----------
              1 user1      firstname1 lastname1 
              2 user2      firstname2 lastname2 
              3 user3      firstname3 lastname3 
              4 user4      firstname4 lastname4 
              5 user5      firstname5 lastname5 
              6 user6      firstname6 lastname6 
              7 user8      firstname8 lastname8 
    
      7 record(s) selected.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    although I agree with tonkuma too, merge is not necessary
    but maybe you have some other logics to deal with
    so I will still give you a merge version:
    Code:
    MERGE INTO emp
    USING (
           select row_number() over (), username, firstname, lastname
           from   (  VALUES ('user4', 'firstname4', 'lastname4')
                           , ('user6', 'firstname6', 'lastname6')
                           , ('user5', 'firstname5', 'lastname5')
                           , ('user7', 'firstname7', 'lastname7') ) x(username,firstname,lastname) 
              where not exists ( select 0 from emp where x.username = emp.username )
        ) AS t (empid, username, firstname, lastname)
    ON emp.username = t.username
    WHEN NOT MATCHED THEN
         INSERT (empid, username, firstname, lastname)
         VALUES (t.empid+(select max(empid) from emp), t.username, t.firstname, t.lastname);

Tags for this Thread

Posting Permissions

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