Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    Unanswered: Issue with deletion of set of records using the cursor with hold

    Hi,
    My requirement is to delete few set of rows based on input keys.

    There will be one input file with set of keys to be deleted
    1234
    5678

    I need to fetch the rows based on input key
    The cursor declaration is as follows
    DECLARE TABLE1_CSR CURSOR WITH HOLD FOR
    SELECT COL1,COL2
    FROM TABLE1
    WHERE COL1=:WS-CURSOR-KEY

    move input key to cursor key
    OPEN TABLE1_CSR
    FETCH TABLE1_CSR
    INTO
    :COL1
    ,:COL2

    Write each record into output file until end of cursor.Say 2 records for this fetch

    DELETE FROM
    TABLE1
    WHERE COL1=:WS-CURSOR-KEY..***Delete 2 records together

    Then it reads the next input key
    But when it tries to fetch, its giving sql code 100 but input key is present in the table.

    Is it because i am deleting the records using the cursor with hold?
    Is it possible to delete set of records keeping the cursor opened?

    Please help.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sqlcode=100 means no row found
    something must be wrong with the predicate variable..
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... Say 2 records for this fetch

    DELETE FROM
    TABLE1
    WHERE COL1=:WS-CURSOR-KEY..***Delete 2 records together
    You can't delete multiple rows at a time by a condition "COL1=:WS-CURSOR-KEY".
    (Note: I assumed that COL1 is UNIQUE or PRIMARY KEY.)

    I guessed that you might fall into similar situation with your last issue.
    http://www.dbforums.com/db2/1683606-...ery-fetch.html
    Last edited by tonkuma; 07-30-12 at 04:32. Reason: Add Note.

Posting Permissions

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