user1933888 user1933888 - 7 months ago 52
SQL Question

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.


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


You can do this as:

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