user1933888 user1933888 - 1 year ago 92
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

Answer Source

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*/
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download