157

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

user1933888
  • 2,897
  • 3
  • 27
  • 36

4 Answers4

272

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*/
Deepshikha
  • 9,896
  • 2
  • 21
  • 21
  • 3
    Can we use alias from the first subquery to the second subquery? In this case the `abc` and `xyz` subqueries? I need to get the result of the first subquery and use it on the second subquery. – Wax Feb 21 '17 at 05:55
  • 1
    @Wax I'm able to use the aliases from the first subquery in the second subquery with no problem, just like the answer above suggests. I was also able to use aliases and columns from both tables in the main query. – cleberz Jan 30 '18 at 01:11
  • Perfect answer. – InfiniteFlash May 21 '20 at 23:26
  • 1
    thanks... I was re-using the keyword "with" before both statements. Your answer clarified my issue. – birwin Nov 16 '20 at 21:01
43

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 Kakirde
  • 4,935
  • 1
  • 13
  • 10
18

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

Srini V
  • 11,045
  • 14
  • 66
  • 89
4

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.

Dave
  • 378
  • 4
  • 14
  • You need to alia t1 within the t2 with clause – Ben May 14 '18 at 19:37
  • How can i reference with clause table inside sub query ? it's throwing an error. for example, WITH SET1 AS (SELECT SYSDATE FROM DUAL), -- SET1 initialised SET2 AS (SELECT * FROM SET1) -- SET1 accessed SELECT * FROM SET2 LEFT OUTER JOIN (select * from SET1 where sysdate = now()) set3 on set1.sysdate = set3.sysdate; – Rupasa Sushma Feb 17 '20 at 08:26