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

Thanks for info good idea. One problem, I need to run this number of times it needs to be run. If the daysfrequency is 2 days than I would need 160 statements (lastcompdate + (daysfrequency ) ... (lastcompdate + (daysfrequency * 160) ) if I wanted it over 2 years would need 320 statements. Any ideas how to cut it down?

respond in group or to ramdan_at_mailexcite.com

In article <3592E854.66700BC6_at_deere.com>, "John P. Higgins" <jh33378_at_deere.com> wrote:
>How about this:
>
>select tasknum
> , (lastcompdate + daysfrequency) duedate
> , decode(daysfrequency
> , 1, 'Daily'
> , 7, 'Weekly'
> , 'Other'
> from ______ where duedate between A and B
>union
>select tasknum
> , (lastcompdate + (daysfrequency * 2) ) duedate
> , decode(daysfrequency
> , 1, 'Daily'
> , 7, 'Weekly'
> , 'Other'
> from ______ where duedate between A and B
>union
>..
>..
>select tasknum
> , (lastcompdate + (daysfrequency * 6) ) duedate
> , decode(daysfrequency
> , 1, 'Daily'
> , 7, 'Weekly'
> , 'Other'
> from ______ where duedate between A and B
>;
>
>
>
>dan wrote:
>
>> I have a table of tasks, each with a due date and a frequency(30 days, 60,
>> 180, ...), so that once the task is done it calculates the next due date
> based
>> on the frequency. Assuming all the tasks are done and completed on the due
>> date, I want a table or report that lists which tasks will be due within a
>> specified time frame, say 1 year. For example, if the frequency is 1 day and
>> my time frame is one week, the task would be due 7 times within the time
>> frame,
>>
>> the table would have :
>>
>> TASKNUM LASTCOMPDATE DAYSFREQUENCY
>> ======== ==================== ============
>> 2 01-MAR-1998 12:00:00 30
>> A sample table or report would list:
>>
>> task due date freq
>>
>> 1 3/1/98 daily
>> 3/2/98
>> 3/3/98 ....
>> 3/31/99
>>
>> 2 3/1/98 monthly
>> 4/1/98...
>> 2/1/99
>> etc
>>
>> I need one SQL statement that will read each row on the table and produce
>> results with multiple rows -- one row for each successive time the task would
>> be due within the time frame.
>>
>> Is this possible with Oracle? Our DB2 guy told us he can do it, but he is on
>> another platform(MVS/mainframe), while we have Oracle 7 on Unix.
>>
>> If not, what other choices are there. We have SQR/REPORTMATE which takes sql
>> and produces reports, the needed sql would feed into it.
>>
>> please respond in group or to Danilo.c.ramirez_at_ucm.com take the nospam out
>> :)
>>
>> Dan Ramriez
>>
>> ramdan_at_mailexcite.com
>> Development Analyst
>> Information Systems
>> Commonwealth Edison
>>
>> The contents of this message express only the sender's opinion. This message
> does not necessarily reflect the policy or views of my employer, Commonwealth
> Edison. All responsibility for the statements made in this Usenet posting
> resides solely and completely with the sender.
>
>
>

Dan Ramriez

ramdan_at_mailexcite.com
Development Analyst
Information Systems
Commonwealth Edison

The contents of this message express only the sender's opinion. This message does not necessarily reflect the policy or views of my employer, Commonwealth Edison. All responsibility for the statements made in this Usenet posting resides solely and completely with the sender. Received on Fri Jun 26 1998 - 15:39:42 CDT