Scope of Local temporary table.

[xyran]

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.

[shawkins]

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

Unlike other sql procedural languages, a Teiid procedure is a self-contained scope, so tables at a session scope are not visible.  You would use a global temporary table instead, which has a definition that is well known, but the data is scoped per session.