Skip to content
Advertisement

How to save data after using With Clause?

I want to save the data I have after With Clause. Maybe saving into TEMP table or some other data set.

WITH TASKLIST AS
 (SELECT * FROM IC_V_NEWSKUSTASKLIST WHERE MSTID IS NULL),
RESULTS AS
 (SELECT *
    FROM ICTASK
    LEFT JOIN TASKLIST
      ON ICTASK.SOURCETABLEID = TASKLIST.SOURCETABLEID)
SELECT * FROM RESULTS;

I want to save RESULTS data into some other table in stand of SELECT them, but the select statement is mandatory after WITH clause.

Advertisement

Answer

CTAS (Create Table As Select) is one option:

SQL> create table test as
  2    with
  3    tasklist as
  4      (select deptno, empno, ename, job, sal
  5       from emp
  6      ),
  7    results as
  8      (select d.dname, t.deptno, t.ename
  9       from tasklist t join dept d on d.deptno = t.deptno
 10       where t.deptno = 10
 11      )
 12    select * from results;

Table created.

SQL> select * from test;

DNAME              DEPTNO ENAME
-------------- ---------- ----------
ACCOUNTING             10 CLARK
ACCOUNTING             10 KING
ACCOUNTING             10 MILLER

SQL>
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement