Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    5

    Exclamation Unanswered: Merge - how to handle matching condition w/o error

    Howdy,

    Just been playing around with using a MERGE to accomplish a
    load of new rec's to a table. I've noticed if ALL of the rec's I'm
    attempted to add are already present in the table, I get the
    following error:

    "SQL0100W No row was found for FETCH, UPDATE or DELETE;
    or the result of a query is an empty table. SQLSTATE=02000"

    Note: the above occurs only if ALL of the rec's are already
    present; if some or none are present, the above error doesn't
    occur.

    How to suppress this error?

    For ref, here's the merge I'm attempting to use:

    MERGE INTO schema.tablename AS A_CurrentUsers
    USING (VALUES (1, 99), (2, 99)
    AS A_PowerUsers (colname1, colname2)
    ON ((A_CurrentUsers.colname1 = A_PowerUsers.colname1) AND
    (A_CurrentUsers.colname2 = A_PowerUsers.colname2))
    WHEN NOT MATCHED THEN
    INSERT (colname1, colname2)
    VALUES( A_PowerUsers.colname1, A_PowerUsers.colname2)
    ELSE IGNORE;

    I was "hoping" the ELSE IGNORE clause could suppress this error ...

    Thanks,

  2. #2
    Join Date
    May 2012
    Posts
    5

    Update

    Howdy,

    Update to my post: The "SQLSTATE=02000" displayed
    (if ALL of the rec's I'm attempted to add are already present
    in the table) is simply a Warning, not an error.

    Lol, the above is the good news & I believe I can live
    with this problem. Bad news? I don't know of a way to
    suppress this warning ...

    Note: I'm attempting to run this merge statement from
    the DB2 Control Center's command editor prompt.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that you can suppress the warning message by adding "CONTINUE HANDLER FOR NOT FOUND ..."
    (exact syntax may be different by your host language).

    On Command Editor:
    Code:
    ------------------------------ Commands Entered ------------------------------
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
     RETURN ;
    
    MERGE INTO employee e
    USING (VALUES '000000') AS f(empno)
      ON  f.empno = e.empno
    WHEN MATCHED THEN
    UPDATE
       SET empno = 'xxxxxx'
    ;
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    DB2(and many other IBM products) may bother you by their plentiful information/warning messages.
    But, it may be not bad, if you know the way to avoid/ignore them.


    Anyway,

    "SQLSTATE=02000" is not specific to MERGE statement.
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE employee
       SET empno = 'xxxxxx'
     WHERE empno = '000000';
    ------------------------------------------------------------------------------
    SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
    query is an empty table.  SQLSTATE=02000
    
    ------------------------------ Commands Entered ------------------------------
    DELETE employee
     WHERE empno = '000000';
    ------------------------------------------------------------------------------
    SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
    query is an empty table.  SQLSTATE=02000

    Another example of warning message.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      rcte(empno) AS (
    VALUES '000000'
    UNION ALL
    SELECT r.empno
     FROM  rcte     r
     WHERE r.empno > '000000'
    )
    SELECT * FROM rcte
    ;
    ------------------------------------------------------------------------------
    
    EMPNO 
    ------
    SQL0347W  The recursive common table expression "DB2ADMIN.RCTE" may contain an 
    infinite loop.  SQLSTATE=01605
    
    000000
    
      1 record(s) selected with 1 warning messages printed.
    Last edited by tonkuma; 05-03-12 at 18:37. Reason: Change the word "ignore" to "suppress".

  4. #4
    Join Date
    May 2012
    Posts
    5
    Howdy tonkuma,

    To begin, thanks for replying. As for using:

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    RETURN;

    When I attempted to do so, I recv'd the following:

    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 "DECLARE" was found
    following "BEGIN ".

    And yes, I'm aware the "SQLSTATE=02000" is not
    specific to a MERGE statement. I was just hoping
    for a successful method to avoid it ..

    Thanks,

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  6. #6
    Join Date
    May 2012
    Posts
    5
    Howdy tonkuma,

    Yes, I changed the statement term. char to use a
    @ character. No change (ie: same error msg returned).

    Thanks for your help,

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It worked well on my environment.

    If I set a statement termination character to semicolon(";") and removed "@" at the end,
    I received errors.
    Code:
    ------------------------------ Commands Entered ------------------------------
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
     RETURN ;
    
    MERGE INTO employee e
    USING (VALUES '000000') AS f(empno)
      ON  f.empno = e.empno
    WHEN MATCHED THEN
    UPDATE
       SET empno = 'xxxxxx'
    ;
    
    END;
    ------------------------------------------------------------------------------
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
     RETURN 
    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 "DECLARE CONTINUE HANDLER FOR NOT FOUND" was 
    found following "BEGIN ".  Expected tokens may include:  "<space>".  LINE 
    NUMBER=2.  SQLSTATE=42601

    But, if I changed a statement termination character to "@" and added "@" at the end,
    it seems work well.
    Code:
    ------------------------------ Commands Entered ------------------------------
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
     RETURN ;
    
    MERGE INTO employee e
    USING (VALUES '000000') AS f(empno)
      ON  f.empno = e.empno
    WHEN MATCHED THEN
    UPDATE
       SET empno = 'xxxxxx'
    ;
    
    END@
    ------------------------------------------------------------------------------
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND
     RETURN ;
    
    MERGE INTO employee e
    USING (VALUES '000000') AS f(empno)
      ON  f.empno = e.empno
    WHEN MATCHED THEN
    UPDATE
       SET empno = 'xxxxxx'
    ;
    
    END
    DB20000I  The SQL command completed successfully.

    Where are the differences between the environment of you and I?

    I am puzzled now.

  8. #8
    Join Date
    May 2012
    Posts
    5
    Howdy tonkuma,

    In response to your last question/statement:

    "Where are the differences between the environment of you and I?
    I am puzzled now."

    1. I honestly don't know the diff's between our DB2 environments
    2. We share the same condition: Lol, we're both puzzled ...

    Thanks for your help,

Posting Permissions

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