Hi,
From here Temp Tables - Teiid 8.12 (draft) - Project Documentation Editor i gathered the scope of local temporary table is per session or a block in virtual procedure.
So I created a Procedure AllEmployees which takes an IN parameter of a tempTableName
BEGIN
DECLARE string VARIABLES.joinedTable = CONCAT(EmployeeView.AllEmployees.tempTableName, '_temp');
BEGIN
EXECUTE IMMEDIATE (((('SELECT GivenName,FamilyName INTO #' || VARIABLES.joinedTable) || ' FROM EmployeeView.EmployeeUnion WHERE EmployeeView.EmployeeUnion.GivenName IN (SELECT GivenName FROM #') || EmployeeView.AllEmployees.tempTableName) || ')');
END
EXECUTE IMMEDIATE (('SELECT * FROM ' || VARIABLES.joinedTable) || '');
END
But when I perform this query
CREATE LOCAL TEMPORARY TABLE tempEmployee (GivenName string, lastName String);
INSERT INTO tempEmployee VALUES ('John','Doe');
SELECT * FROM (EXEC EmployeeView.AllEmployees('tempEmployee')) as X;
I get exception
TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (((('SELECT GivenName,FamilyName INTO #' || VARIABLES.joinedTable) || ' FROM EmployeeView.EmployeeUnion WHERE EmployeeView.EmployeeUnion.GivenName IN (SELECT GivenName FROM #') || EmployeeView.AllEmployees.tempTableName) || ')')" with the SQL statement "(((('SELECT GivenName,FamilyName INTO #' || VARIABLES.joinedTable) || ' FROM EmployeeView.EmployeeUnion WHERE EmployeeView.EmployeeUnion.GivenName IN (SELECT GivenName FROM #') || EmployeeView.AllEmployees.tempTableName) || ')')" due to: Group does not exist: #tempEmployee
So is this because tempEmployee is already out of scope? Is there a way to pass temp table names to stored procedure so I can join on the temp tables? The Temp tables contain values which otherwise would be in a WHERE IN(...) clause.
Thanks.