Alternative for "connect by" Oracle SQL

dohan_rivas 0
Viewed 45

Anyone be able to give me some script that does the same as the below but not using "connect by"? The code works well, but I cannot use connect by in the BI publisher.

select to_char(to_date('2022-05-01') + (level -1),'YYYY-MM-DD') as read_date 
from dual 
connect by to_date('2022-05-01') + (level -1) <= to_date('2022-05-05')
oracle sql
1 Answers

CONNECT BY is Oracle's propriatary and concise way to write a recursive query. You can replace it with a standard SQL compliant recursive query that has been supported by Oracle since 2002.

with read_dates(read_date) as
(
  select date '2022-05-01' from dual
  union all
  select read_date + interval '1' day from read_dates
  where read_date < date '2022-05-05'
)
select to_char(read_date, 'YYYY-MM-DD')
from read_dates;

Two remarks:

  • Your own code is vulnarable, because it uses an implicit string to date conversion (to_date('2022-05-01')) that relies on session date settings and can thus fail miserably.
  • It is rare that we select dates as strings (to_char(..., 'YYYY-MM-DD')), because we usually want our app to know that we are selecting dates.
Thorsten Kettner0
Related