Creating CTEs in Oracle

Question

I am trying to create a CTE in Oracle that does not select from an existing table, but instead inserts data into it. I am currently creating a table and then deleting it after running query . Is there a way to create a CTE that effectively does the same? This is my current code:

 create table RTG_YEARS (YR date); insert into RTG_YEARS values (to_date('2013-01-01', 'yyyy-mm-dd')); insert into RTG_YEARS values (to_date('2013-12-31', 'yyyy-mm-dd')); insert into RTG_YEARS values (to_date('2014-01-01', 'yyyy-mm-dd')); insert into RTG_YEARS values (to_date('2014-12-31', 'yyyy-mm-dd')); insert into RTG_YEARS values (to_date('2015-01-01', 'yyyy-mm-dd')); insert into RTG_YEARS values (to_date('2015-12-31', 'yyyy-mm-dd')); 

Answer 1

You can create a general table expression (CTE, factoring subquery , etc.) by selecting date values ​​from a double and combining them all together:

 with RTG_YEARS (YR) as ( select to_date('2013-01-01', 'yyyy-mm-dd') from dual union all select to_date('2013-12-31', 'yyyy-mm-dd') from dual union all select to_date('2014-01-01', 'yyyy-mm-dd') from dual union all select to_date('2014-12-31', 'yyyy-mm-dd') from dual union all select to_date('2015-01-01', 'yyyy-mm-dd') from dual union all select to_date('2015-12-31', 'yyyy-mm-dd') from dual ) select * from RTG_YEARS; YR ---------- 2013-01-01 2013-12-31 2014-01-01 2014-12-31 2015-01-01 2015-12-31 

Not related to the fact that this is a CTE, but you can slightly reduce the number of input using date literals :

 with RTG_YEARS (YR) as ( select date '2013-01-01' from dual union all select date '2013-12-31' from dual union all select date '2014-01-01' from dual union all select date '2014-12-31' from dual union all select date '2015-01-01' from dual union all select date '2015-12-31' from dual ) select * from RTG_YEARS;