CODEWITHSUNDEEP
sqloracle-database
Akshay Lokur
Akshay Lokur

Reputation: 7516

Can I nest "WITH" clause in Oracle SQL?

Following query gives me an error:

"ORA-32034: Unsupported use of WITH clause"

 WITH table_B as 
(
    SELECT * FROM (
        WITH table_A AS
            (SELECT 'Akshay' as NAME FROM DUAL)
        SELECT NAME FROM table_A
    ) WHERE NAME LIKE '%Aks%' ---<<< Note a filter here
)
SELECT * from table_B;

Is there a way out? Thanks

Upvotes: 9

Views: 11668

Answers (2)

Akshay Lokur
Akshay Lokur

Reputation: 7516

We can use like following:-

WITH 
table_A AS
            (SELECT 'Akshay' as NAME FROM DUAL),
table_B AS
            (SELECT * FROM table_A where NAME like 'Aks%') --<< Adding filter here now
SELECT * FROM table_B;

Cheers!

Upvotes: 1

Pham X. Bach
Pham X. Bach

Reputation: 5442

You should change your query to:

WITH table_a AS
(
    SELECT 'Akshay' as name 
    FROM dual
)
,table_b AS 
(
    SELECT name 
    FROM table_a
    WHERE name LIKE '%Aks%'
)
SELECT * 
FROM table_b;

Upvotes: 14

Related Questions