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

Question

I had a very simple question: does the 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 request work by repeating the same request several times, but I do not want to do this and use "WITH AS". This seems like a simple requirement, but the oracle does not allow me:

ORA-00928: missing SELECT keyword

Answer 1

You can do it like:

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

Answer 2

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; 

Answer 3

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

Answer 4

Aditya or others, can you join or match t2 with t1 in your example, i.e. translate 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) 

It is unclear whether only WHERE is supported for joining or which join approach is supported inside the second WITH object. Some of the examples have WHERE A = B down in the body of the selection "below" WITH clauses.

The error I get after these WITH declarations is identifiers (field names) in B that are not recognized, in the body of the rest of SQL. Thus, WITH syntax works fine, but does not have access to the results from t2.