Re: How to Get Every two weeks settlement --allways Wednesday

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 29 Jul 2006 09:57:27 -0700
Message-ID: <1154192246.427778@bubbleator.drizzle.com>

Charles Hooper wrote:

> rjayanth_at_gmail.com wrote:

>> Hi,
>>
>> I am trying to get solution for this. I need to get settlement every
>> two weeks and it alwyas happens on "Wednesday"
>>
>> Example :To Day's Date Jul 19
>>
>> My output look like this
>>
>> Date Day
>>
>> Jul19 Wed
>> Aug 2 Wed
>> Aug 16 Wed
>> Aug 30 Wed
>> ....so on till End Date which is 30 years
>>
>> Kindly let me know ASAP
>>
>> Oracle Version:Oracle 9i, restriction no PL/SQL
>>
>>
>> Regards
>> Jay

> 
> You need a table with at least 780 rows in order to seed a counter,
> then it is just a matter of multiplying the counter by 14 to hit every
> other week: DBA_OBJECTS, USER_OBJECTS, etc.  For example:
> 
> SELECT
>   (TO_DATE('2006-07-19', 'YYYY-MM-DD') + (ROWNUM - 1) * 14) NEXT_DATE
> FROM
>   DBA_OBJECTS
> WHERE
>   ROWNUM<=780;
> 
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Not necessarily. Why not a pipelined table function that could be joined in the query (no table and no maintenance ... ever).

There is a demo PTF in Morgan's Library at www.psoug.org that shows how one is written and used in a join.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jul 29 2006 - 11:57:27 CDT