CODEWITHSUNDEEP
sqloracle-database
user1933888
user1933888

Reputation: 3037

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

Upvotes: 169

Views: 347329

Answers (4)

Srini V
Srini V

Reputation: 11375

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

Upvotes: 21

Dave
Dave

Reputation: 388

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.

Upvotes: 5

Aditya Kakirde
Aditya Kakirde

Reputation: 5215

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;

Upvotes: 48

Deepshikha
Deepshikha

Reputation: 10284

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*/

Upvotes: 301

Related Questions