Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Error in creating procedure having temp table..!

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    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

    Originally posted by ARWinner
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

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