Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is there any alternative way to connect by clause

Hi,

I'm trying to split date ranges into months by using connect by clause. This approach is working if the number of rows are less.
When the rowcount is increasing, ending up with a timeout error. Could you please suggest me if there is any other approach for this requirement?

select level r,
greatest( add_months(trunc(sdate,'mm'),level-1), sdate ),
least( last_day( add_months(sdate,level-1) ), edate )
from (select to_date( :sdate, 'dd-mon-yyyy' ) sdate,
to_date( :edate, 'dd-mon-yyyy' ) edate
from dual)
connect by level <= months_between( trunc( edate,'mm'), trunc(sdate,'mm') ) + 1

This post has been answered by BluShadow on Jul 18 2023
Jump to Answer
Comments
Post Details
Added on Jul 17 2023
11 comments
373 views