Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    45

    Unanswered: Error running multiple deletes within a procedure

    For some reason I am unable to perform multiple deletes in a procedure.
    I get the error below when trying to create the procedure.

    Please help?

    Code:
    CREATE PROCEDURE DEV.UPDATE_CAT (  ) 
    LANGUAGE SQL 
    SPECIFIC DEV.UPDATE_CAT 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    PROGRAM TYPE SUB 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX 
    
    DELETE FROM  CT_CUSTOMER_REP CR
    WHERE NOT EXISTS ( 
    SELECT 1 
    FROM CT_REPPACK_STAGE ST 
    WHERE ST . CUSTOMER_NO = CR . CUSTOMER_NO 
    );
    
    
    DELETE FROM  CT_CUSTOMER_FOCUS CF
    WHERE NOT EXISTS ( 
    SELECT 1 
    FROM CT_REPPACK_STAGE ST 
    WHERE ST . CUSTOMER_NO = CF . CUSTOMER_NO 
    );
    
    DELETE FROM  CT_CUSTOMER_SEGMENT CS
    WHERE NOT EXISTS ( 
    SELECT 1 
    FROM CT_REPPACK_STAGE ST 
    WHERE ST . CUSTOMER_NO = CS . CUSTOMER_NO 
    );
    
    MERGE INTO DEV. CT_CUSTOMER_REP CR
    
    USING ( SELECT REP_ID, CUSTOMER_NO, REP_EFF_END_DATE, REP_EFF_START_DATE FROM CT_REPPACK_STAGE ) STG
    
    ON 
    CR . REP_ID = STG . REP_ID 
    AND
    CR . CUSTOMER_NO = STG . CUSTOMER_NO
    
    WHEN MATCHED THEN
    UPDATE SET CR . EFF_START_DATE = STG . REP_EFF_START_DATE, CR . EFF_END_DATE = STG . REP_EFF_END_DATE
    
    WHEN NOT MATCHED THEN 
    INSERT VALUES ( STG . REP_ID , STG . CUSTOMER_NO , STG . REP_EFF_START_DATE, STG . REP_EFF_END_DATE ) 
    
     
    GRANT ALTER , EXECUTE   
    ON SPECIFIC PROCEDURE DEV.UPDATE_CAT
    TO DEV



    Return Code = 0


    SQL State: 02000
    Vendor Code: 100
    Message: [SQL0100] Row not found for DELETE. Cause . . . . . : One of the following conditions has occurred: -- If this is a FETCH statement, no more rows satisfy the selection values (end of file). The name of the cursor is DELETE and the result set identifier is 0. If the result set identifier is non-zero, the result table for this cursor was being accessed as a stored procedure result set. -- If this is a FETCH statement for a scrollable cursor, a record was not found. If NEXT was specified, end of file was reached. If PRIOR was specified, the beginning of the file was reached. If RELATIVE was specified, either the beginning of file or the end of file was reached, depending on the value specified. If FIRST or LAST was specified, then no records satisfy the selection criteria. The name of the cursor is DELETE. -- If this is an embedded SELECT statement, no rows satisfy the selection values. -- If this is an UPDATE, INSERT, DELETE, or MERGE statement, no rows satisfy the subselect, WHERE clause, or MERGE criteria. No rows were updated, inserted, or deleted. -- If SKIP LOCKED DATA is specified, no available rows qualified for return. Recovery . . . : No recovery is necessary.
    Last edited by robasc; 09-24-15 at 22:20. Reason: ADDED ENTIRE PROCEDURE

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Showing the whole procedure code would help
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    Mar 2007
    Posts
    45
    Okay I updated the code to include the entire procedure.

    I have tried removing/adding semicolons to the code so there is a few not in place.

  4. #4
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    SQL0100 is warning when no matching records were found for DELETE. Add error handling to ignore "NOT FOUND" condition. See following link for more details.

    https://www-01.ibm.com/support/knowl.../c0009028.html

  5. #5
    Join Date
    Mar 2007
    Posts
    45
    awesome,

    so that's it, just add the variables to the body of the procedure huh?

    I will give it a try tomorrow. I'm very new to db2 so sometimes the messages are pretty unclear to me.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if being very new, a good way to start is to have a look at the doc
    http://www-01.ibm.com/support/knowle...7.html?lang=en
    or at the samples shipped with db2 in the samples directory
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Mar 2007
    Posts
    45
    Well I tried it out this morning with the variables but I had to prepend the variable with the @ character.

    After trying it, it worked but I was wondering why since I'm not calling the variables anywhere. I did notice that I am not using BEGIN and END in my procedure so I tried it without the variables and added BEGIN and END and it worked.
    I'm curious to know why it is working with BEGIN and END?

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
  •