Temporary table in WITH Clause (CTE) is not working

Help Procedural SQL
1

Hi Team,

In Procedure, I have created Temporary Table and am trying to use that temporary table in WITH clause (CTE).

But am getting the below error.

Error Code: 2242 Unhandled exception Type: ER_USER_RAISE (2242) Message: MYDB.PROC_INTEG line 449:
** ER_TEMPORARY_TABLE_INSIDE_CTE (1894), Temporary table MYDB.MIG_TEMP_TABLE_280220221047209289 cannot appear in the definition of common table expression. **
** Callstack: #0 Line 495 in MYDB.PROC_INTEG**

Kindly help me to resolve the issue or give me the alternate solution.

Query :-
WITH BAS_QUERY AS
(
SELECT * FROM MIG_TEMP_TABLE_280220221047209289
WHERE LIV_FLG=‘A’
)
SELECT * FROM FIN_TRANSACTION A , BAS_QUERY B
WHERE A.ID=B.ID

2

Hi ganesh! I have also escalated this issue internally as well. Thanks again for your patience.

3

We have a feature request open for this and I took note that you are hitting this issue. The workaround for now is to use a regular table, not a temp table. Then you have to be sure to clean it up when you’re done.

1 Like
4

Thanks for the update.

1 Like