Query with a "With" Clause inside a For Loop Cursor of DB2 PL SQL

Question

I have a very complex query that includes a "With" clause. This query works fine when executed on the DB2 Client. But if the same query is used inside a For Loop Cursor of a PL SQL stored procedure it does not work. On trying to apply the stored procedure to the database, it gives a syntax error as shown below.

SQL0104N  An unexpected token "AS" was found following "col5 )

The for loop is as shown below.

FOR records AS cursors CURSOR FOR
  (
    WITH
    temp1
    (
        col1, col2, col3, col4, col5
    )
    AS 
    (
        SELECT
        col1, col2, col3, col4, col5
    FROM
        table1
    )
    WITH
    temp2
    (
        col6, col7, col8, col9, col10
    )
    AS
    (
        SELECT
        col6, col7, col8, col9, col10
    FROM
        table2
    )
    SELECT col1, col2, col3, col4, col5, col6, co7, col8, col9, col10
    FROM temp1, temp2
)
DO
    -- Do Something here.
END FOR;

Can you please help solve this problem. Thanks in advance.

Answer 1

You have two problems. First, the FOR statement is incorrect; it should refer to a previously declared cursor:

...
CURSOR mycur IS WITH ... SELECT ...;
...
FOR rec IN mycur LOOP ...

Second,the query

WITH temp1 ( col1, col2, col3, col4, col5 ) AS ( 
  SELECT col1, col2, col3, col4, col5 FROM table1 
) 
WITH temp2 ( col6, col7, col8, col9, col10 ) AS ( 
  SELECT col6, col7, col8, col9, col10 FROM table2

is invalid and would never run by itself, so your claim that it executes in the CLP is untrue.

Answer 2

Finally I got this thing working. The solution was fairly simple. It was to remove the braces enclosing the query as shown below.

 FOR records AS cursors CURSOR FOR 

    WITH
    temp1
    (
        col1, col2, col3, col4, col5
    )
    AS 
    (
        SELECT
        col1, col2, col3, col4, col5
    FROM
        table1
    )

    WITH
    temp2
    (
      col6, col7, col8, col9, col10
    )
    AS
    (
        SELECT
        col6, col7, col8, col9, col10
    FROM
        table2 
    )
    SELECT col1, col2, col3, col4, col5, col6, co7, col8, col9, col10
    FROM   temp1, temp2
DO
    --  Do Something here.

END  FOR  ;

I am not really sure why it happens like this. It works just fine for any other normal query without a WITH clause in it.