I have the following dates in a table cp:
start_date: 01/01/2017, end_date: 01/08/2017;
start_date: 01/04/2017, end_date: 01/07/2017
I would like to create a record dynamically for each month between these periods. It tried with the following query, but I don't understand why it's not working.
select add_months(cp.end_date, rownum-1) which_month, id
FROM (select '1' as id,
to_date('01/01/2017', 'DD/MM/YYYY') start_date,
to_date('01/08/2017', 'DD/MM/YYYY') end_date
from dual
UNION
select '2' as id,
to_date('01/04/2017', 'DD/MM/YYYY') start_date,
to_date('01/07/2017', 'DD/MM/YYYY') end_date
from dual) cp, all_objects
WHERE ROWNUM <= months_between(cp.end_date, add_months(cp.start_date, -1));
Can you help me?
I don't understand why it's not working.
ROWNUM is a pseudo-column generated by the result set; it doesn't work the way you think.
One solution - there are several but this is the closest there is to an industry standard - is to use the connect by level trick:
select '1' as id,
add_months(date '2017-01-01', (level-1) ) as which_month
from dual
connect by level <= months_between(date '2017-08-01', date '2017-01-01')+1
months_between() takes the arguments (end_date, start_date) - you need to add one to that number to get the end date.
"Would the level-trick still work ?"
Sort of. There's an additional trick necessary to prevent the CONNECT BY generating a product:
select id,
add_months(start_date, level-1 ) as which_month
from t23
connect by level <= months_between(end_date , start_date)+1
-- these two lines required to avoid the need for DISTINCT
and id = prior id
and prior sys_guid() is not null
order by 1, 2
;