Volatile table creation issue within Stored procedure

Database

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
6 REPLIES

Re: Volatile table creation issue within Stored procedure

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?

N/A

Re: Volatile table creation issue within Stored procedure

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...

Re: Volatile table creation issue within Stored procedure

Thank you very much. 

N/A

Re: Volatile table creation issue within Stored procedure

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

Re: Volatile table creation issue within Stored procedure

I am trying to use cast function in my select query inside procedure. Output will be fetched using cursor. QUERY: SET MYSQL = ' SELECT A.logdate, A.username, CAST(B.SqlTextInfo AS VARCHAR(4000)) AS sqltextinfo, FROM PDCR TABLE; '

 

Though i have used cast function i am not getting the full sql text.

 

 

N/A

Re: Volatile table creation issue within Stored procedure

Well, SQLTextInfo is defined as VarChar(31000) in dbc.DBQLSQLTbl, you're truncationg it to a VarChar(4000).