Alternative for "connect by" Oracle SQL-CodePudding

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')

CodePudding user response：

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.