I was trying to to check if it is possible to create a volatile table in Teradata by taking the out put of a stored procedure. Here is the sample code and the error I am saying. Please let me know if it is possible in Teradata or am I doing something wrong here.
REPLACE PROCEDURE schema1.stored_proc1 (N INTEGER)
DYNAMIC RESULT SETS 1 SQL SECURITY INVOKER
DECLARE queryInput VARCHAR(1000);
DECLARE resultSet CURSOR WITH RETURN ONLY FOR sqlStatement;
SET queryInput = 'SELECT top ' || N || ' * from schema1.table1';
PREPARE sqlStatement FROM queryInput;
The stored procedure works fine when I tried to call it independently but when I try calling it while creating a volatile table it throws an error shown below. I wanted to check if it is possible to do it Teradata or do I need to do it differently.
CREATE MULTISET VOLATILE TABLE vt_tmp1
CALL schema1.stored_proc1 (10)
WITH DATA NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS;
Throws an error - "Create Table Failed. 3707: Syntax error, expected something like a 'CHECK' keyword between '(' and the 'CALL' keyword."