Hello,What exact error you are facing?If there is no error and you are not getting any rows as output, the reason may be missing "ON COMMIT PRESERVE ROWS" clause after "CREATE VOLATILE TABLE table_name".Additionally, there is no need to define parent database for volatile tables, as a matter of fact they are not created in perm-space!HTH!Regards,Adeel
-- If you are stuck at something .... consider it an opportunity to think anew.
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
Fiddled with it some and now no error, but also no records in Test2... CREATE VOLATILE TABLE Test1(location_id INTEGER);CREATE VOLATILE TABLE Test2(location_id INTEGER);INSERT INTO Test1 VALUES(1000);INSERT INTO Test1 VALUES(2000);INSERT INTO Test1 VALUES(3000);INSERT INTO Test1 VALUES(4000);FOR rec AS cLocations CURSOR FOR SELECT top 2 location_id FROM Test1;DO INSERT INTO Test2 VALUES(rec.location_id);END FOR SELECT * FROM Test2;
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 compilationerror 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. rgs
"On commit preserve rows" version executed without error... Unfortunately, it still yields an empty result set when selecting from either table1 or table2.Attempting to add Begin Transaction after the Create Table statement immediately before the FOR LOOP yielded a "expected something like a name..." type error.Would someone be so kind as to provide a working example, or even better, modify my example code above so it is a functioning version?Also, using Teradata SQL Assistant, how does one change between Teradata mode or Ansi mode?
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()BEGINFOR rec AS cLocations CURSOR FOR SELECT top 2 location_idFROM MYUSERID.Test1DOINSERT INTO MYUSERID.Test2 VALUES(rec.location_id);END FOR;ENDCALL MyTest1();-- 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
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)CREATE VOLATILE TABLE PFM_TD_User.Test2(location_id INTEGER)FOR rec AS cLocations CURSOR FOR SELECT top 2 location_id FROM PFM_TD_User.Test1DO INSERT INTO PFM_TD_User.Test2 VALUES(rec.location_id);END FOR