Hi All,

I Just now joined this community so first first a big Hello to all of you.

I have a quick query. As per my understanding a stored procedure in netezza can return a result set. My tried following steps

(1) Create a temprary table i.e abc
    CREATE TEMPORARY TABLE abc (i VARCHAR(20), i2 VARCHAR(20));

(2) Create a stored procedure and write sql statement(inside stored procedure) to insert data into temporary table.

CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS REFTABLE(abc) LANGUAGE NZPLSQL AS
BEGIN_PROC
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
RETURN REFTABLE;
END;
END_PROC;

(3) return result set from temporary table using return reftable command.

EXECUTE PROCEDURE returntwo(now());

When i tried to compile stored procedure, got following error. When i use a normal table instead of temporary table then stored procedure works fine but in case of stored procedure, it doesn't even compile. Pls help . I am using 5.0.8 version.

Error Code: 0, SQL State: ]  ERROR:  Expected a table for <DB Name>.<User Name>.abc