ChatGPT解决这个技术问题 Extra ChatGPT

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


D
Deepshikha

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

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 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.
Perfect answer.
thanks... I was re-using the keyword "with" before both statements. Your answer clarified my issue.
A
Aditya Kakirde

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;

"you can access columns of t1 here as well" was very helpful to point out.
S
SriniV

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


D
Dave

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.


You need to alia t1 within the t2 with clause
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;