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>