Can I nest "WITH" clause in Oracle SQL?

Question

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

Answer 1

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;

Answer 2

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!