Expand|Select|Wrap|Line Numbers
- CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT )
- RESULT SETS 1
- LANGUAGE SQL
- BEGIN part1
- DECLARE TOTAL_LEFT INT DEFAULT 0;
- SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S;
- WHILE (TOTAL_LEFT > 0)
- DO
- BEGIN part2
- INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(
- TRANSACTION_REFERENCE,
- VERSION,
- CDM
- )(
- SELECT TRANSACTION_REFERENCE,
- VERSION,
- CDM
- FROM DBSAMBA.REPORTEDTRANSACTION_S
- FETCH FIRST 1000 ROWS ONLY);
- BEGIN part3
- DECLARE Trans_Ref VARCHAR(20);
- DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000;
- DECLARE c1 CURSOR
- FOR
- SELECT TRANSACTION_REFERENCE
- FROM DBSAMBA.REPORTEDTRANSACTION_S
- FETCH FIRST 1000 ROWS ONLY;
- SET norows=0;
- OPEN c1;
- while norows <1000
- FETCH c1 INTO Trans_Ref;
- DELETE
- FROM DBSAMBA.REPORTEDTRANSACTION_S
- WHERE TRANSACTION_REFERENCE =Trans_Ref;
- END WHILE;
- close c1;
- END part3;
- COMMIT;
- SELECT COUNT(*)INTO TOTAL_LEFT
- FROM DBSAMBA.REPORTEDTRANSACTION_S;
- END part2;
- END WHILE;
- END part1;
I get following error :-
Expand|Select|Wrap|Line Numbers
- REATE PROCEDURE DBSAMBA.PROC1(OUT norows INT )
- LANGUAGE SQL
- BEGIN
- DECLARE TOTAL_LEFT INT DEFAULT 0
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "L_LEFT
- INT DEFAULT 0". Expected tokens may include: "<psm_semicolon>". LINE
- NUMBER=4. SQLSTATE=42601
- SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
- SQL0206N "TOTAL_LEFT" is not valid in the context where it is used.
- SQLSTATE=42703
- WHILE (TOTAL_LEFT > 0) DO BEGIN PART2 INSERT INTO DBSAMBA.REPORTEDTRANSACTION_D(TRANSACTION_REFERENCE, VERSION, CDM ) (SELECT TRANSACTION_REFERENCE, VERSION, CDM FROM DBSAMBA.REPORTEDTRANSACTION_S FETCH FIRST 1000 ROWS ONLY)
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "WHILE (TOTAL_LEFT > 0) DO BEGIN PART2" was
- found following "BEGIN-OF-STATEMENT". Expected tokens may include:
- "<space>". SQLSTATE=42601
- BEGIN PART3 DECLARE Trans_Ref VARCHAR(20)
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "BEGIN PART3" was found following
- "BEGIN-OF-STATEMENT". Expected tokens may include: "<create_proc>".
- SQLSTATE=42601
- DECLARE EXIT HANDLER FOR NOT FOUND SET norows = 1000
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "<space>" was found following "EXIT". Expected
- tokens may include: "HANDLER". SQLSTATE=42601
- DECLARE c1 CURSOR FOR SELECT TRANSACTION_REFERENCE FROM DBSAMBA.REPORTEDTRANSACTION_S FETCH FIRST 1000 ROWS ONLY
- DB20000I The SQL command completed successfully.
- SET norows=0
- 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 "NOROWS" is not valid in the context where it is used.
- SQLSTATE=42703
- OPEN c1
- DB20000I The SQL command completed successfully.
- while norows < 1000 FETCH c1 INTO Trans_Ref
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "while norows < 1000" was found following
- "BEGIN-OF-STATEMENT". Expected tokens may include: "<create_proc>".
- SQLSTATE=42601
- DELETE FROM DBSAMBA.REPORTEDTRANSACTION_S WHERE TRANSACTION_REFERENCE =Trans_Ref
- 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 "TRANS_REF" is not valid in the context where it is used.
- SQLSTATE=42703
- END WHILE
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
- WHILE". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
- close c1
- DB21030E The cursor "C1" has not been opened.
- END PART3
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
- PART3". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
- COMMIT
- DB20000I The SQL command completed successfully.
- SELECT COUNT(*)INTO TOTAL_LEFT FROM DBSAMBA.REPORTEDTRANSACTION_S
- SQL0206N "TOTAL_LEFT" is not valid in the context where it is used.
- SQLSTATE=42703
- END PART2
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
- PART2". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
- END WHILE
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END
- WHILE". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
- END
- DB21034E The command was processed as an SQL statement because it was not a
- valid Command Line Processor command. During SQL processing it returned:
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
- Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
- SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".
- Explanation:
- A syntax error in the SQL statement or the input command string for the
- SYSPROC.ADMIN_CMD procedure was detected at the specified token
- following the text "<text>". The "<text>" field indicates the 20
- characters of the SQL statement or the input command string for the
- SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.
- As an aid, a partial list of valid tokens is provided in the SQLERRM
- field of the SQLCA as "<token-list>". This list assumes the statement is
- correct to that point.
- The statement cannot be processed.
- User response:
- Examine and correct the statement in the area of the specified token.
- sqlcode: -104
- sqlstate: 42601