Menu
I am trying to create a CTE in Oracle that doesn't select from an existing table but instead has data inserted into it. Currently, I am creating a table and then dropping it after the query is done. Is there a way to create a CTE that effectively does the same thing? 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'));
208
A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.
Oracle UsageCommon Table Expressions (CTE) provide a way to implement the logic of sequential code or to reuse code. You can define a named sub query and then use it multiple times in different parts of a query statement.
Advantages of Using CTE CTE can be more readable: Another advantage of CTE is CTE is more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using a subquery. Also, people tend to follow logic and ideas easier in sequence than in a nested fashion.
CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE a view, as part of the view's SELECT query.
You can create your common table expression (CTE, subquery factoring, etc.) by selecting the date values from dual, and unioning 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 it being a CTE, but you can reduce the typing a bit by 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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
