Reusing subquery within oracle query

Question

I have the same query listed as subquery multiple times within a larger Oracle SQL query. Is there a way to define it once and reuse it using a shorter form?

Community · Accepted Answer · 2017-05-23 10:30:06Z

4

You can make use of common table expressions.

Copied query from here: Creating a CTE in Oracle

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
cross join RTG_YEARS;

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 6, 2017 at 17:06

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Punter Vicky Over a year ago

Thank you very much , @Evaldas!

Collectives™ on Stack Overflow

Reusing subquery within oracle query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related