The WITH clause is part of a SELECT statement, and the scope of the named subqueries is local to the SELECT from which they were defined.
It seems that your code has many errors. I remarked some of them, but there could be more.
WITH TEMP_All AS
(SELECT * FROM TEMP1
SELECT * FROM TEMP2
SELECT * FROM TEMP3)
FROM TEMP_ALL <b>– *** MISSING SEMICOLON </b>
IF iCount > 0 THEN
TEMP_OUT AS( <b>– *** MISSING WITH KEYWORD </b>
SELECT Key1, count(*) as cnt <b>– *** THIS REQUIRES A GROUP BY CLAUSE</b>
FROM TEMP_ALL), <b>– *** TEMP_ALL IS OUT OF SCOPE HERE</b>
SELECT BarSuid FROM TEMP_OUT
WHERE cnt = (SELECT MAX(cnt) FROM TEMP_OUT)); <b>– *** SELECT STATEMENT MISSING </b>ELSE
SELECT Key1 FROM PRODUCT
WHERE Key1 NOT IN ( SELECT Key1 FROM PRODUCT_DET));
WHERE PRODUCT.Key1 = TempKey.Key1
AND TempKey.Key1 IS NOT NULL;
EXCEPTION WHEN OTHERS THEN
I would recommend to make some basic tests with the ‘WITH’ clause, and once you get it to work, include it in more complex queries.