All Forums Database
Nikhil_Teradata 24 posts Joined 10/13
11 Jun 2015
Volatile table creation issue within Stored procedure

Hi, 
 
I have below code

REPLACE PROCEDURE Personal.test_code()
BEGIN


CREATE MULTISET  VOLATILE TABLE Test_Temp  (URL VARCHAR(1000), EXTR VARCHAR(50))
ON COMMIT PRESERVE ROWS;

INSERT INTO Test_Temp (URL)
VALUES ('http://www.ABC.com/event/0022330');

SELECT * FROM Test_Temp;

INSERT INTO Test_Temp (EXTR)
SELECT SUBSTRING (Test_Temp.URL FROM 3 FOR 4);

SELECT * FROM Test_Temp;

END;

I get below messages when I run.

 [5526] SPL5000:W(L13), E(3807):Object 'Test_Temp' does not exist.

 [5526] SPL1045:E(L15), Invalid or missing INTO clause.

 [5526] SPL5000:W(L18), E(3807):Object 'Test_Temp' does not exist.

 [5526] SPL1045:E(L20), Invalid or missing INTO clause.

 

I do not understand why the messages. I have used almost exact code to create and operate on the volatile table and that does not give any issues at all.

 

-Nik
 

--Nik

"Learn, Learn, Learn and your life would never be boring"

Nikhil_Teradata 24 posts Joined 10/13
11 Jun 2015

A bit further analysis showed that the actual error is 
[5526] SPL1045:E(L15), Invalid or missing INTO clause.
Do I have to create a table on DB(not volatile) and save the contents there? Does TD not allow to use select on Volatile table within a stored procedure?
 

--Nik

"Learn, Learn, Learn and your life would never be boring"

dnoeth 4628 posts Joined 11/04
12 Jun 2015

Hi Nik,
you can't select from any kind of table in a Stored Proc. 
You need to use cursor-syntax:

DECLARE c CURSOR WITH RETURN ONLY FOR
SELECT * FROM Test_Zemp;

OPEN c;

http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_Reference/B035_1148_015K/ch05.140.53.html

Dieter

Nikhil_Teradata 24 posts Joined 10/13
17 Jun 2015

Thank you very much. 

--Nik

"Learn, Learn, Learn and your life would never be boring"

unnati 1 post Joined 12/15
11 Dec 2015

Hi,
 
How can I create a table using SELECT and INSERT INTO statement from another table?
Is it possible to UPDATE a table within a stored procedure?
 
Thanks

You must sign in to leave a comment.