Search code examples
db2

DB2 Assign result of with clause to variable

I am using DB2 LUW and want to a assign a result of a With clause to a variable in a stored procedure.

I got the exception

{0:0} An unexpected token "AS" was found following "l = (WITH BASE".  Expected tokens may include:  "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

Is it possible to assign the result on this way or should I have to solve it with a cursor?

DECLARE result CLOB(8M); 
SET result = (WITH BASE AS ( 
xxx
) 
SELECT JSON_ARRAY (select json_objects FROM ITEMS format json) FROM SYSIBM.SYSDUMMY1);
Solution

  • Use instead the syntax style:

    with ctename AS ( ... ) SELECT ... INTO ... FROM ctename;