USING WITH CLAUSE AND CREATE TABLE

PHIL Sawyer
PHIL Sawyer used Ask the Experts™
on
Hi
I want to create a table where I have used With clause but keep getting an error.
First of all - is it possible and if so then how.
For the sake of simplicity see below as my actual sql is long.

WITH T1 AS
( SELECT 'TEST' AS AA, 25 AS NUM FROM DUAL)

CREATE TABLE TEST_ AS

SELECT AA, NUM FROM T1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Move the syntax around and it should work.

This worked for me:
create table bob as
with mydata as (
	select 'hello' col1 from dual
)
select * from mydata
/

Open in new window

Author

Commented:
Great - thanks

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial