CODEWITHSUNDEEP
sqloracle-databaseplsqlwith-statement
aakashgupta.0205
aakashgupta.0205

Reputation: 687

Use for loop after the With Clause in PL/SQL

Im using PL/SQL. I am trying to have a for loop right after I define my temporary tables in the with clause. However, Im getting an error to have a SELECT query first.

For instance

WITH TMP1 AS (.....), TMP2 AS (......), TMP3 AS (......)

FOR R IN (SELECT DISTINCT ..... FROM TMP1 WHERE .....)
LOOP
SELECT .... FROM TMP2, TMP2 WHERE TMP2.... = R..... ....

How do I go about doing so?

Thanks

Upvotes: 5

Views: 9677

Answers (2)

Ramesh
Ramesh

Reputation: 851

try creating a new table using this query. you can delete the table after you have done with your work.

Upvotes: 0

user330315
user330315

Reputation:

You can't access a CTE outside of the whole statement. And you can't access individual parts of a CTE outside of the final SELECT for a CTE.

You need to put the whole CTE (including the final SELECT statement) into the cursor loop:

FOR R IN (WITH TMP1 AS (.....), 
               TMP2 AS (......), 
               TMP3 AS (......)
          SELECT DISTINCT ..... 
          FROM TMP1 
             JOIN temp2 ON ... 
             JOIN temp3 ON ... 
          WHERE .....)
LOOP
   -- here goes the code that processes each row of the query
END LOOP;

Upvotes: 9

Related Questions