Creating a table in Oracle using a query that contains a WITH clause within it

Question

I am basically trying to run a create table statement using a query that has a with clause within it, but I am getting an error. Is there a different way to run this? The query statement is something like this:

CREATE TABLE DATA_TABLE AS ( WITH X AS (.....)

SELECT * FROM X )

I would appreciate any help. Thanks.

Answer 1

Here's what you want.

CREATE TABLE t
AS 
WITH some_data AS ( 
   SELECT 1 as some_value 
   FROM dual

   UNION ALL 

   SELECT 2 
   FROM dual
) 
SELECT * 
FROM some_data