|
Hi All,
I am new to Stored Procedures, Cursors. I am getting an error "DB21034E The command was processed as an SQL statement because it was invalid Command Line Processor command. During SQL processing it returned SQL0104N An unexpected token "FOR" was found following "FOR P_DYN_STMT1” Expected tokens may include: "FROM". LINE NUMBER=13. SQLSTATE=42601" when I try to create this stored procedure in DB2 UDB
CREATE PROCEDURE CER_MKTG.P_CHNL_FLG_SET_ALL_LOB()
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
DECLARE P_STMT1 VARCHAR(1000);
DECLARE P_STMT2 VARCHAR(1000);
DECLARE P_CHNL VARCHAR(100);
DECLARE P_LOB VARCHAR(100);
DECLARE A VARCHAR(100);
DECLARE P_RC INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR P_DYN_STMT1;
FOR LOBF, CHANNELF AS SELECT DISTINCT LOB, CHANNEL_FLAG FROM CER_MKTG.SUPPRESSION_GRID
DO
SET P_STMT1 = '';
SET P_CHNL = '';
SET P_LOB = '';
SET P_STMT1 = 'UPDATE CER_MKTG.LOB_SUPPRESSION_TEST SET ';
SET P_CHNL = CHANNELF;
SET P_LOB = LOBF;
SET P_STMT1 = P_STMT1 || P_CHNL || '_FLG = ''Y'' WHERE SUPPRESSION_TYPE = '''|| P_LOB ||''' AND (';
SET P_STMT2 = 'SELECT SCRUB_NAME FROM CER_MKTG.SUPPRESSION_GRID WHERE LOB = '''|| P_LOB || ''' AND
CHANNEL_FLAG = ''' || P_CHNL || '''';
SET P_RC = (SELECT COUNT(*) FROM CER_MKTG.SUPPRESSION_GRID WHERE LOB = P_LOB AND CHANNEL_FLAG = P_CHNL);
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(CHAR(P_RC));
PREPARE P_DYN_STMT1 FROM P_STMT2;
OPEN C1;
SET A = '';
FETCH C1 INTO A;
WHILE (SQLCODE=0) DO
SET P_STMT1 = P_STMT1 || ' ' ||A || ' +';
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(CHAR(P_RC));
SET P_RC = P_RC - 1;
SET A = '';
FETCH C1 INTO A;
END WHILE;
CLOSE C1;
SET P_STMT1 = LEFT(P_STMT1,LENGTH(P_STMT1)-1) || ' ) = 0';
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(P_STMT1);
INSERT INTO CER_MKTG.SCRUB_STATEMENT VALUES(P_STMT2);
PREPARE P_DYN_STMT2 FROM P_STMT1;
SET P_STMT2 = '';
SET P_STMT2 = 'ALTER TABLE CER_MKTG.LOB_SUPPRESSION_TEST ACTIVATE NOT LOGGED INITIALLY';
PREPARE P_DYN_STMT1 FROM P_STMT2;
EXECUTE P_DYN_STMT1;
EXECUTE P_DYN_STMT2;
SET P_STMT2 = '';
SET P_STMT2 = 'COMMIT';
PREPARE P_DYN_STMT1 FROM P_STMT2;
EXECUTE P_DYN_STMT1;
END FOR;
END!
Any thoughts why I am getting this error and how to resolve it?
Thanks in advance,
Saravanan
| 
