I want to save the data I have after With Clause. Maybe saving into TEMP table or some other data set.
x
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>