I am trying to create a procedure containing the following declaration for temporary table:-

DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS
(SELECT t.seq_nbr, t.id, k.amt, k.qty)
FROM SESSION."#TEMP1" AS t LEFT OUTER JOIN emp AS k ON t.id = k.id AND
(v_settleDate IS NOT NULL) AND k.pdate = v_settleDate AND (v_SrcId IS NOT NULL) AND k.id = v_SrcId
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

v_settleDate, v_SrcId and #TEMP1 are declared at the beginning of the procedure.
I would like to know if we can create the temporary table in DB2 on the basis of referring/comparing column values to the local variable defined in the procedure.??

Recieving the following error message while loading:-

C:\Manish>db2 -td! -f file
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0206N "V_SETTLEDATE" is not valid in the context where it is used. LINE NUMBER=23. SQLSTATE=42703

if I remove that reference to setteldate it gives same message for V_SRCID.

Thanks in advance,
Manish

What happens if you declare it this way:

DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS
(SELECT t.seq_nbr, t.id, k.amt, k.qty)
FROM SESSION."#TEMP1" AS t LEFT OUTER JOIN emp AS k ON t.id = k.id AND
(k.pdate IS NOT NULL) AND k.pdate = v_settleDate AND (k.id IS NOT NULL) AND k.id = v_SrcId
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

If you still get the error, could you post the entire SP.

Andy

Hi Andy,

I have tried doing it that way and also by removing one of the two references but it is not working that way. It is giving error if the variable is there.
Yes, if we remove the sentence fully
"k.pdate IS NOT NULL) AND k.pdate = v_settleDate AND (k.id IS NOT NULL) AND k.id = v_SrcId"
then it gets loaded, but then the logic is changed.

Please find below the full procedure:-
=============================================
CREATE PROCEDURE sel_proc(
IN v_id INT,
IN v_SrcId INT)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE v_status_cd INT;
DECLARE v_settleDate TIMESTAMP;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP1" AS
(SELECT a.seq_nbr, a.sip, b.name, b.iv_typ, c.eff_date,
a.qty, a.amt
FROM emp_d AS a, basic_d AS b, debt_d AS c
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS
(SELECT t.seq_nbr, t.sip, t.name, t.iv_typ, t.eff_date,
t.qty, t.amt, epip.src_id,
epip.src_amt, epip.yrs_qty
FROM SESSION."#TEMP1" AS t LEFT OUTER JOIN ext_pro AS epip ON
t.sip = epip.sip AND
(v_settleDate IS NOT NULL) AND epip.prc_src_date = v_settleDate AND
(v_SrcId IS NOT NULL) AND epip.src_id = v_SrcId
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
BEGIN
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT DISTINCT t1.*, rae.seq_nbr AS excpt_seq_nbr
FROM SESSION."#TEMP2" AS t1 LEFT OUTER JOIN reg_ag_ext AS rae ON
rae.seq_nbr = t1.seq_nbr AND
(v_id IS NOT NULL) AND rae.rep_id = v_id
ORDER BY t1.seq_nbr ;

SET v_status_cd = 0;
SELECT cor_name
INTO v_settleDate
FROM TABLE (SELECT ra.st_date, ROW_NUMBER () OVER ()
FROM repo_agrmt AS ra
WHERE ra.rep_id = v_id ) AS temp_table(cor_name, rn)
WHERE temp_table.rn <= 1;
SET l_sqlcode = 0;
INSERT INTO SESSION."#TEMP1"
SELECT a.seq_nbr, a.sip, b.name, b.iv_typ, c.eff_date,
a.qty, a.amt
FROM emp_d AS a, basic_d AS b, debt_d AS c
WHERE a.rep_id = v_id AND a.sip = b.sip AND
b.sip = c.sip;
SET v_status_cd = l_sqlcode;
IF v_status_cd = 0 OR v_status_cd = 100 THEN
SET l_sqlcode = 0;
INSERT INTO SESSION."#TEMP1"(seq_nbr, sip, name, iv_typ,
eff_date, qty, amt)
SELECT a1.seq_nbr, a1.sip,
coalesce(b1.name, 'NOT AVAILABLE'),
coalesce(b1.iv_typ_cd, '????'), b1.m_date,
a1.qty, a1.amt
FROM emp_d AS a1 LEFT OUTER JOIN re_basic AS b1 ON
a1.sip = b1.sip
WHERE (v_id IS NOT NULL) AND a1.rep_id = v_id AND
a1.sip NOT IN (SELECT sip
FROM SESSION."#TEMP1");
COMMIT;
SET v_status_cd = l_sqlcode;
END IF;
IF v_status_cd = 0 OR v_status_cd = 100 THEN

SET l_sqlcode = 0;
INSERT INTO SESSION."#TEMP2"
SELECT t.seq_nbr, t.sip, t.name, t.iv_typ,
t.eff_date, t.qty, t.amt, epip.src_id,
epip.src_amt, epip.yrs_qty
FROM SESSION."#TEMP1" AS t LEFT OUTER JOIN ext_pro AS epip ON
t.sip = epip.sip AND
(v_settleDate IS NOT NULL) AND epip.prc_src_date = v_settleDate AND
(v_SrcId IS NOT NULL) AND epip.src_id = v_SrcId ;
SET v_status_cd = l_sqlcode;
END IF;
IF v_status_cd = 0 OR v_status_cd = 100 THEN
SET l_sqlcode = 0;
OPEN temp_cursor;
SET v_status_cd = l_sqlcode;
END IF;

RETURN v_status_cd;
END;
END!
==============================================

Thanks,

Manish

I think I may see the problem. It might be because you have those conditions in the Jion clause. Try it this way:

DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS
(SELECT t.seq_nbr, t.id, k.amt, k.qty)
FROM SESSION."#TEMP1" AS t LEFT OUTER JOIN emp AS k ON t.id = k.id where
(k.pdate IS NOT NULL) AND k.pdate = v_settleDate AND (k.id IS NOT NULL) AND k.id = v_SrcId
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

Andy