Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Help with simple cursor on DB2 9.5

    Hi,

    I am new to DB2 and cursor development and am running into a problem with what I consider a simple enough cursor. When I run the example below I get this error:

    "SQL0206N "V_USER_ID" is not valid in the context where it is used. LINE NUMBER=12..."

    Code:
    CREATE PROCEDURE DB2INST1.Proc1()
    LANGUAGE SQL
    P1: BEGIN
        DECLARE v_USER_ID VARCHAR(20);
        DECLARE c1 CURSOR FOR SELECT USR_ID FROM USR_APPL_PRFL_ASSN WHERE APP_NM = 'ABC1';
      
        OPEN c1;
        
        LOOP
        FETCH c1 INTO v_USER_ID;
      
        INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (7, v_USR_ID);
    
        INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (8, v_USR_ID);
          
        END LOOP;
        
        CLOSE c1;
    END P1
    ;
    Basically I am trying to take the output from the select statement and use that then as a parameter in my insert statements.
    What is wrong here? I am using RapidSQL to run this.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You declared as V_USER_ID and tried to use it as V_USR_ID.

    Andy

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    Thanks! Sometimes these things need another set of eyes.

    How can I run this cursor without creating a procedure? I've tried just running this but I get a lot of errors around unexpected tokens and end-of-statements etc:

    Code:
    BEGIN
        DECLARE v_USER_ID VARCHAR(20);
        DECLARE c1 CURSOR FOR SELECT USR_ID FROM USR_APPL_PRFL_ASSN WHERE APP_NM = 'ABC1';
      
        OPEN c1;
        
        LOOP
        FETCH c1 INTO v_USER_ID;
      
        INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (7, v_USR_ID);
    
        INSERT INTO USR_WRK_GRP(WRK_GRP_ID, USR_ID) VALUES (8, v_USR_ID);
          
        END LOOP;
        
        CLOSE c1;

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot. Cursors are only allowed in Stored Procedures.

    Andy

  5. #5
    Join Date
    Aug 2010
    Posts
    4
    Thanks. If an insert for a particular row fails, because of primary key violation, is there a way for the cursor to ignore this and continue with the next row?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by davidjoyce View Post
    Thanks. If an insert for a particular row fails, because of primary key violation, is there a way for the cursor to ignore this and continue with the next row?
    Yes, you will need to DECLARE a CONTINUE HANDLER for that particular SQLCODE.

    By the way, what you are trying to accomplish can (and should) be done with a single INSERT ... SELECT... statement.

  7. #7
    Join Date
    Aug 2010
    Posts
    4
    How would this be done?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like this:
    Code:
    insert into USR_WRK_GRP(WRK_GRP_ID, USR_ID)
     select t.num, u.usr_id 
     from USR_APPL_PRFL_ASSN u, table(values 7,8) t (num) 
     where u.app_nm = 'ABC1' 
     and not exists (
      select 1 from USR_WRK_GRP where usr_id = u.usr_id
     )

Posting Permissions

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