[sql] Can we have multiple "WITH AS" in single sql - Oracle SQL

I had a very simple question: Does oracle allow multiple "WITH AS" in a single sql statement.

Example:

WITH abc AS( select ......)

WITH XYZ AS(select ....) /*This one uses "abc" multiple times*/

Select ....   /*using XYZ multiple times*/

I can make the query work by repeating the same query multiple times, but do not want to do that, and leverage "WITH AS". It seems like a simple requirement but oracle does not allow me:

ORA-00928: missing SELECT keyword

the correct syntax is -

with t1
as
(select * from tab1
where conditions...
),
t2
as
(select * from tab2
where conditions...
(you can access columns of t1 here as well)
)
select * from t1, t2
where t1.col1=t2.col2;

Aditya or others, can you join or match up t2 with t1 in your example, i.e. translated to my code,

with t1 as (select * from AA where FIRSTNAME like 'Kermit'),
     t2 as (select * from BB B join t1 on t1.FIELD1 = B.FIELD1)

I am not clear whether only WHERE is supported for joining, or what joining approach is supported within the 2nd WITH entity. Some of the examples have the WHERE A=B down in the body of the select "below" the WITH clauses.

The error I'm getting following these WITH declarations is the identifiers (field names) in B are not recognized, down in the body of the rest of the SQL. So the WITH syntax seems to run OK, but cannot access the results from t2.

Yes you can...

WITH SET1 AS (SELECT SYSDATE FROM DUAL), -- SET1 initialised
     SET2 AS (SELECT * FROM SET1)        -- SET1 accessed
SELECT * FROM SET2;                      -- SET2 projected

10/29/2013 10:43:26 AM

Follow the order in which it should be initialized in Common Table Expressions