Create table with as and insert into with as statements in oracle
Oracle supports the use of with as clauses to create tables
Syntax:
Create table table_name as
With clause_name as
(select query)
[, clause_name1 as
(select query).]
Select column1,...columnn from clause_name
Syntax:
Insert into table_name
With clause_name as
(select query)
[, clause_name1 as
(select query).]
Select column1,...columnn from clause_name
Example:
-- create with statement
CREATE TABLE w_test_20 AS WITH data_info_19 AS (SELECT NVL (ename,'unknow employee') AS ename, DECODE (deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','no this department') AS dname, NVL (job,'unkown job') AS job, substr (to_char (hiredate,'yyyy-mm-dd'), 1mae 7) AS hiredate CASE WHEN sal 1000 AND sal 2000 AND sal 3000 THEN'A 'ELSE' unkown grade' END AS grade, ROUND (sal,2) AS sal, FLOOR (nvl (comm,0)) AS comm, NVL (sal,0) + NVL (comm,0) AS total_sal FROM emp WHERE deptno IN (SELECT DISTINCT deptno FROM dept) SELECT * FROM data_info_19 CREATE TABLE w_test_18 AS WITH dept_info AS (SELECT deptno AS deptno_1,dname FROM dept), emp_info AS (SELECT empno AS empno_1,ename,job,deptno FROM emp), other_info AS (SELECT empno,hiredate,NVL (sal,0) + NVL (comm,0) AS total_sal FROM emp) SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno INSERT INTO w_test_19WITH data_info_19 AS (SELECT NVL (ename,'unknow employee') AS ename, DECODE (deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','no this department') AS dname, NVL (job,'unkown job') AS job, substr (to_char (hiredate,'yyyy-mm-dd'), 1mae 7) AS hiredate CASE WHEN sal 1000 AND sal 2000 AND sal 3000 THEN'A 'ELSE' unkown grade' END AS grade, ROUND (sal,2) AS sal, FLOOR (nvl (comm,0)) AS comm, NVL (sal,0) + NVL (comm,0) AS total_sal FROM emp WHERE deptno IN (SELECT DISTINCT deptno FROM dept) SELECT * FROM data_info_19 INSERT INTO w_test_18 WITH dept_info AS (SELECT deptno AS deptno_1,dname FROM dept), emp_info AS (SELECT empno AS empno_1,ename,job,deptno FROM emp), other_info AS (SELECT empno,hiredate,NVL (sal,0) + NVL (comm,0) AS total_sal FROM emp) SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno
