Can we have multiple WITH AS in single sql - Oracle SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Can we have multiple "WITH AS" in single sql - Oracle SQL

Examples related to sql

Examples related to oracle