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.

Why are you executing the statement in a loop? SQL is usually much better at working in sets. — Clockwork-Muse, Dec 11 '13 at 13:40
No specific reason as such. The data returned by the SQL query will be modified inside the loop. There are various transformation rules that i have to apply and i thought having results processed record by record gave me a lot of control over the data. Hence used the method as described above. — Abhi, Dec 11 '13 at 18:26

score 0 · Answer 1 · edited Dec 10 '13 at 20:27

0

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.

edited Dec 10 '13 at 20:27

Peter Schuetze

16,185
4
44
58

answered Dec 09 '13 at 22:10

mustaccio

18,234
16
48
57

With the kind of For Loop that i have used here, DB2 implicitly creates a cursor on which i can loop over. I need not explicitly declare it. With the query that i have written i was trying to narrow down on the problem rather than writing the exact query. There is of course a select following the with clause in my above query. Please check my edit and let me know if it makes sense now. – Abhi Dec 10 '13 at 02:58
The query is still invalid, and the `FOR` statement syntax is still wrong. You may want to check the manual for the correct syntax of both. – mustaccio Dec 10 '13 at 12:26
I have used the same For Loop construct as given in the below link. And it does work without any problems. Is there still something wrong. http://stackoverflow.com/questions/15198577/how-to-get-db2-9-7-cursor-value-in-for-loop-where-returned-value-is-a-string – Abhi Dec 10 '13 at 18:57

score 0 · Answer 2 · edited Jan 04 '14 at 09:03

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.

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

2 Answers2