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

rjayanth (AT) gmail (DOT) com wrote:
It's not comprehensible. Restate it differently and give at least your
tables description.

rjayanth (AT) gmail (DOT) com schreef:

What else? Would external programs do? Java? Pure SQL?

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On 28 Jul 2006 10:17:00 -0700, rjayanth (AT) gmail (DOT) com wrote:

Three solutions

1 Pipelined function, no table. Not allowed because pl/sql
2 dummy table, populated by an anonymous block
begin
for i in 1 .. ((30*52)/2) loop
insert into foo values (sysdate + 14 * i, to_char(sysdate+14*i,
'day');
end loop;
end;
/
but ... pl/sql
3
insert into foo values (sysdate + 14 * 1, to_char(sysdate+14*1,
'day');
insert into foo values (sysdate + 14 * 2, to_char(sysdate+14*2,
'day');
insert into foo values (sysdate + 14 * 3, to_char(sysdate+14*3,
'day');
etc

No pl/sql, but 750 commands.

Hopefully you see how utterly stupid the restriction is.

--
Sybrand Bakker, Senior Oracle DBA

rjayanth (AT) gmail (DOT) com wrote:
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.

Charles Hooper wrote:
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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Sat, 29 Jul 2006 09:57:27 -0700, DA Morgan <damorgan (AT) psoug (DOT) org>
wrote:

The 'restriction' to this quiz question was 'NO Pl/sql'

--
Sybrand Bakker, Senior Oracle DBA

DA Morgan schrieb:
I suggest, all more or less popular approaches to generate rows are
listed here:
http://orafaq.com/wiki/Oracle_Row_Generator_Techniques

Best regards

Maxim

Sybrand Bakker wrote:
Then forget I suggested it. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
There is no table to maintain in my example - any table with a
sufficient number of rows will work to seed the counter. My example
uses the DBA_OBJECTS table, which is not the best choice, but will
exist in all Oracle databases (only accessible to users with DBA
rights). A smaller table with just enough rows is a better choice.
Also, the number 780 (52 / 2 * 30) needs to be adjusted. A year is
roughly 52 weeks, 1.24 days, so 783 is probably a better number to use
[ CEIL(52 / 2 * YEARS + 1.24 / 14 * YEARS) ].

There is no need for a complicated solution when a simple SQL statement
with a little math thrown in accomplishes the task - that is not to say
that complicated solutions should not be offered (thanks for the tip on
pipelined table functions - I might find a use for that somewhere).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.