Trouble creating table in oracle with multiple sub queries?

The Sandbox Software/Technology
1

In Oracle sql i have a query like the following which runs fine.

with first as
( select … ),
second as
( select … ),
third as
( select … )
select * from third

Can i create a table with this? I have write access in my own schema but get an error about unsupported use of a WITH clause. Is there a work around?

create table end_result as
(
with first as
( select … ),
second as
( select … ),
third as
( select … )
select * from third
)

2

I think you need some sort of “connecting” statement between the sub-select statements (and no comma and “redefinition”):

with first as
(select . . . )
UNIONALL
(select . . .)
UNIONALL
.
.
.
UNIONALL
(select . . .)
select * from first

3

Would that work? In my example above the “second” statement has “first” in the from, and the “third” has “second” in the from.

In any event, i found a work around. Thanks!

1 Like
4

Glad you found the work around (mind sharing a summary?).

But I wonder if it’s the form of the query (i.e., the underlying database doesn’t know how to do multiple aliased select statements) that the issue more than the structure.

Perhaps trying it w/o the commas?