Oracle | "ORA-00942: table or view does not exist" in with-clause

Question

I need to write a query as below - query given is just for concept.

I am getting ORA-00942: table or view does not exist for view table inner_nested_table used in with clause.

First, is it legal to use it like this? If no, is there any work-around I can use.

select
    inner_nested_table.column1,
    inner_nested_table.column2,
    inner_nested_table.column3,
    (
        with test as (
            select
                column4, column5
            from
                inner_nested_table
        )
        select column4 from test
    ) columnX
from
    (
    select
        column1,
        column2,
        column3,
        column4,
        column5
    from
        actual_table
    ) inner_nested_table;

Answer 1

The test sub-query is nested too deeply for the SQL engine to find the inner_nested_table. Oracle supports finding aliases nested one level apart but not two.

Instead, you can use:

WITH inner_nested_table AS (
  select column1,
         column2,
         column3,
         column4,
         column5
  from   actual_table
),
test AS (
  select column4
  from   inner_nested_table
)
select column1,
       column2,
       column3,
       (select column4 from test) AS columnX
from   inner_nested_table;

db<>fiddle here