Oracle create table using with clause

sql oracle common-table-expression
Rnet picture Rnet · Mar 25, 2011 · Viewed 54.7k times · Source

Can I create a table from a query formed using with clause?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Mar 25, 2011

Sure: 

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

Related questions

Creating a CTE in Oracle

I am trying to create a CTE in Oracle that doesn't select from an existing table but instead has data inserted into it. Currently, I am creating a table and then dropping it after the query is done. Is there …

Read More
Insert into from CTE

WITH DTL AS (SELECT CMPI_CODE, CMN_CDTY_MTRL, CMI_WT_FACTOR, CMI_CNTRCT_RATE, 'PL', PRESENT_PRICE, TRM_CODE, ROUND(((NVL(PRESENT_PRICE,1)*CMI_WT_FACTOR) / CMI_CNTRCT_RATE),2) AS PL_FACTOR FROM VW_CMD_MATERIAL WHERE TRM_CODE = 41) …

Read More
Difference between WITH clause and subquery?

What is the difference between WITH clause and subquery? 1. WITH table_name as ( ... ) 2. select * from ( select curr from tableone t1 left join tabletwo t2 on (t1.empid = t2.empid) ) as temp_table

Read More