CODEWITHSUNDEEP
sqloracle-database
dohan_rivas
dohan_rivas

Reputation: 93

Alternative for "connect by" Oracle SQL

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

Upvotes: 0

Views: 353

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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.

Upvotes: 3

Related Questions