Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Unanswered: What's problem with the stored proc?

    Hi there,

    A stored proc as the following is created, but error is occured:

    ADMIN.TEMP_TABLE_TEST: 17: [IBM][CLI Driver][DB2/6000] SQL0206N "A.RL_ID" is not valid in the context where it is used. LINE NUMBER=17. SQLSTATE=42703

    What's problem with it?

    Thanks,

    Stored proc:

    CREATE PROCEDURE ADMIN.TEMP_TABLE_TEST (IN wk_end_date timestamp)
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN


    INSERT INTO ADMIN.TEMP_SARS
    SELECT location_id, CONCAT (CONCAT (first_name, ' '), last_name) AS sar_name
    FROM ADMIN.LOCATION_CONTACT rlc , ADMIN.CONTACT c
    WHERE title_code = 'SAR' AND rlc.contact_id = c.contact_id;

    DECLARE cursor1 CURSOR FOR

    SELECT a.rl_id, a.sar_name
    FROM ADMIN.TEMP_SARS a;

    OPEN cursor1;

    END P1

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try moving the declare cursor to right after the BEGIN statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    61
    I tried your suggestion and get the following error:

    ADMIN.TEMP_TABLE_TEST: 10: [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "ADMIN" was found following " INSERT INTO". Expected tokens may include: "JOIN". LINE NUMBER=10. SQLSTATE=42601

    The proc now looks like this:

    CREATE PROCEDURE ADMIN.TEMP_TABLE_TEST (IN wk_end_date timestamp)
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE cursor1 CURSOR FOR

    INSERT INTO ADMIN.TEMP_SARS
    SELECT location_id, CONCAT (CONCAT (first_name, ' '), last_name) AS sar_name
    FROM ADMIN.LOCATION_CONTACT rlc , ADMIN.CONTACT c
    WHERE title_code = 'SAR' AND rlc.contact_id = c.contact_id;

    SELECT a.rl_id, a.sar_name
    FROM ADMIN.TEMP_SARS a;

    OPEN cursor1;

    END P1

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry, I meant move the entire declare cursor statement.

    CREATE PROCEDURE ADMIN.TEMP_TABLE_TEST (IN wk_end_date timestamp)
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE cursor1 CURSOR FOR
    SELECT a.rl_id, a.sar_name
    FROM ADMIN.TEMP_SARS a;

    INSERT INTO ADMIN.TEMP_SARS
    SELECT location_id, CONCAT (CONCAT (first_name, ' '), last_name) AS sar_name
    FROM ADMIN.LOCATION_CONTACT rlc , ADMIN.CONTACT c
    WHERE title_code = 'SAR' AND rlc.contact_id = c.contact_id;

    OPEN cursor1;

    END P1
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2004
    Posts
    61
    I did as you suggested and get the following error:

    ADMIN.TEMP_TABLE_TEST: 9: [IBM][CLI Driver][DB2/6000] SQL0206N "A.RL_ID" is not valid in the context where it is used. LINE NUMBER=9. SQLSTATE=42703

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you check if the column is in the table

    describe table ADMIN.TEMP_SARS

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2004
    Posts
    61
    The table was created as follows and the column does exist.

    CREATE TABLE TEMP_SARS(
    rl_id INTEGER NOT NULL,
    sar_name VARCHAR (65) NOT NULL
    );

  8. #8
    Join Date
    Mar 2004
    Posts
    448
    Please check with the describe statement.

    regards,

    mujeeb

Posting Permissions

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