I have a leases table, with start and end date of each lease. Some leases don’t have an end date, and the field is NULL.
I want to generate a list of the payment dates/amounts over the duration of the lease. For the leases with an end date, I’m using CONNECT BY LEVEL <= TRUNC(MONTHS_BETWEEN(pl.END_DATE, pl.START_DATE)+1,0).

However if I try to handle the case when the end_date is NULL, then the query goes on forever.

Sample data, lease 2 works fine and generates 13 rows, while lease 4 just goes on forever.

I’ve tried various iterations of replacing the NULL with the current date, as an output field and in the results.

SELECT  CASE WHEN rownum = 1 THEN pl.START_DATE ELSE ADD_MONTHS(trunc(pl.START_DATE, 'mon') + pl.PAYMENT_DAY -1, rownum -1) END AS PAYMENT_DATE
         , pl.*
         , rownum AS pm_num
FROM    PM_LEASES pl  
WHERE   pl.LEASE_ID = 4
CONNECT BY LEVEL <= TRUNC(MONTHS_BETWEEN(nvl(pl.END_DATE, trunc(sysdate)), pl.START_DATE)+1,0)

Also tried reading the Oracle documentation, however it focuses on actual hierarchies.I’m using this as a workaround for the moment, which works if the lease is a 100 payments or less

SELECT  pl.lease_id
        , CASE WHEN lvl = 1 THEN pl.first_payment_amt ELSE pl.lease_amt END AS lease_amt
        , CASE WHEN lvl = 1 THEN pl.start_date ELSE ADD_MONTHS(trunc(pl.START_DATE, 'mon') + pl.PAYMENT_DAY -1, lvl-1) END AS payment_date
FROM    pm_leases pl
LEFT JOIN (select level as lvl from dual connect by level <= 100) ON 1=1
WHERE   add_months(pl.start_date, lvl) <= nvl(pl.end_date, add_months(trunc(sysdate), 1))