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

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

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;

You can do this as:

WITH abc AS( select
             FROM ...)
, XYZ AS(select
         From abc ....) /*This one uses "abc" multiple times*/
  Select 
  From XYZ....   /*using abc, XYZ multiple times*/

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.