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

Try moving the declare cursor to right after the BEGIN statement.

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

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

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

Can you check if the column is in the table

describe table ADMIN.TEMP_SARS

Cheers
Sathyaram

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
);

Please check with the describe statement.

regards,

mujeeb