Re: Help with doing recursion in oracle: how to have sql go thru list of values

If I were you I would use a calendar table consisting of one column containing date values.

create table calendar (calendar_day date);

Insert in this table all dates, for example, from January 1st 1998 till December 31rd 2020.

runs the following under SQL*PLUS :

declare

    cal_date date;
begin

    cal_date:=to_date('19980101','YYYYMMDD'); loop

   exit when cal_date>to_date('20203112','YYYYMMDD');    insert into calendar values (cal_date);    commit;
   cal_date:=cal_date+1;
end loop;
end;
/

And then, for example :

select cal.calendar_day from calendar cal, task t where t.tasknum=2 and trunc(t.lastcompdate)=to_date('19980301','YYYYMMDD') and mod((cal.calendar_day-trunc(t.lastcompdate)),t.frequency)=0 and (cal.calendar_day-trunc(t.lastcompdate))>0; Received on Thu Jun 25 1998 - 13:54:20 CDT