Hello there. I'm trying unsuccessfully to use a for loop. Can anyone offer insight as to why this doesn't work?
CREATE VOLATILE TABLE PFM_TD_User.Test1(location_id INTEGER) INSERT INTO PFM_TD_User.Test1 VALUES(1000) INSERT INTO PFM_TD_User.Test1 VALUES(2000) INSERT INTO PFM_TD_User.Test1 VALUES(3000) INSERT INTO PFM_TD_User.Test1 VALUES(4000) INSERT INTO PFM_TD_User.Test1 VALUES(5000)
I had to leave out some of my code above in order for the forum to accept my post. That's why there are missing semi-colons in my example. Once I put those back in, I get the follow error:
"3707: Syntax error, expected something like a name or a Unicode delimited identifier or a 'TRANSACTION' keyword or a 'FASTEXPORT' keyword or a 'LOADING' keyword between the 'END' keyword and the 'FOR' keyword
I assume you created the volatile table outside the stored procedure, because you said you were not getting an error. According to documentation:
“If the cursor SELECT statement references a missing database object, an SPL compilation error is reported whether or not the creator is the immediate owner of a stored procedure.”
If you look up how volatile tables work it states that by default that it is emptied at the end of a transaction. If your session is in Teradata mode then each statement is a separate transaction. So your 4 inserts would insert and end the transaction and empty the table. What you want is for your create statement to say:
CREATE VOLATILE TABLE Test1(location_id INTEGER) ON COMMIT PRESERVE ROWS;
Adeel tried to tell you that.
The other option is to logon your session in ANSI mode so that you have to execute an explicit COMMIT to end the transaction. The third option is to embed the statements into an explicit transaction by surrounding the statements with a BEGIN TRANSACTION… END TRANSACTION when using a Teradata mode session.
This is what I could make to work ...(in Teradata mode)
CREATE VOLATILE TABLE MYUSERID.Test1(location_id INTEGER) ON COMMIT PRESERVE ROWS;
INSERT INTO MYUSERID.Test1 VALUES(1000); INSERT INTO MYUSERID.Test1 VALUES(2000); INSERT INTO MYUSERID.Test1 VALUES(3000); INSERT INTO MYUSERID.Test1 VALUES(4000); INSERT INTO MYUSERID.Test1 VALUES(5000);
CREATE VOLATILE TABLE MYUSERID.Test2(location_id INTEGER) ON COMMIT PRESERVE ROWS;
REPLACE PROCEDURE MyTest1() BEGIN FOR rec AS cLocations CURSOR FOR SELECT top 2 location_id FROM MYUSERID.Test1 DO INSERT INTO MYUSERID.Test2 VALUES(rec.location_id); END FOR; END
-- got two records SELECT * FROM TEST2;
For connecting as ANSI, go to your ODBC administrator, pick your DSN, click on options and change the session mode drop down to ANSI