I am migrating from MS SQL Server to IBM DB2 (Version 9.7). Trying to write a compound SQL While Loop with local variables (Not a part of Stored Procedure), something like -
BEGIN ATOMIC
DECLARE i INT DEFAULT 12;
WHILE i > 0
"DO ....";
SET COUNT = COUNT - 1;
END WHILE;
END
But I get an error just in first line for declaring variables-
SQL0104N An unexpected token "12" was found following "ECLARE I INT DEFAULT". Expected tokens may include: "END-OF-STATEMENT"
Any help is greatly appreciated.
I assume that your problem has to do with statement terminator (easier to see if you provide the actual errors you get). I tested the following and it worked:
[ ... ]$ cat aa.sql
CREATE TABLE TEMP_ITERATIONS ( X VARCHAR(50) ) @
BEGIN ATOMIC
DECLARE i INT DEFAULT 12;
WHILE i > 0 DO
INSERT INTO TEMP_ITERATIONS VALUES ('IT'|| RTRIM(CHAR(i)));
SET i = i - 1;
END WHILE;
END @
[ ... ]$ db2 -td@ -f aa.sql
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
[ ... ]$ db2 "select * from TEMP_ITERATIONS"
X
--------------------------------------------------
IT12
IT11
IT10
[...]
12 record(s) selected.
Here I used @ as a statement terminator since ; has a special meaning. If you don't want to change the statement terminator a trick is to "hide" the ; inside the compound statement by adding a comment at the end of the line:
[ ... ]$ cat aa.sql
CREATE TABLE TEMP_ITERATIONS ( X VARCHAR(50) ) ;
BEGIN ATOMIC
DECLARE i INT DEFAULT 12; --
WHILE i > 0 DO
INSERT INTO TEMP_ITERATIONS VALUES ('IT'|| RTRIM(CHAR(i))); --
SET i = i - 1; --
END WHILE; --
END ;
[ ... ] db2 -tf aa.sql
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
[ ... ]$ db2 "select * from TEMP_ITERATIONS"
X
--------------------------------------------------
IT12
IT11
IT10
[...]
12 record(s) selected.