For loop problem

Database
Enthusiast

For loop problem

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.Test1
DO
INSERT INTO PFM_TD_User.Test2
VALUES(rec.location_id);
END FOR
6 REPLIES
Teradata Employee

Re: For loop problem

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
Enthusiast

Re: For loop problem

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
Enthusiast

Re: For loop problem

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;
rgs
Enthusiast

Re: For loop problem

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.

rgs
Enthusiast

Re: For loop problem

"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?
Enthusiast

Re: For loop problem

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

CALL 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