Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: table containing the days for a week

Re: table containing the days for a week

From: Kevin Crosbie <>
Date: Wed, 27 Apr 2005 15:33:09 +0200
Message-ID: <1114608795.3d6a16a8f0746c688eba77ffba087902@teranews>

<> wrote in message
> Well it might have been helpful to clarify that this was suggestion for
> a version earlier than 9i.

My reply to your solution quoted you with "> If you have 9i or up you could use a view like this"
Mine was intended as a follow on to your answer. True I didn't specify, but in reality this solution works for all versions of oracle, not just pre 9i.

> While the subject mentions "a week", reading the actual question
> reveals that the OP needed 40 years of weekdays not seven days. Also
> the OP already had a solution based on a table with the number of days
> needed and was seeking an alternative which is what I provided.

I realize that, however he did specify he wanted a week at a time. His solution was based on a table that was customized to be a calendar, not just any table against which you can select rows. In his question he never says he needs the 40 years in one select, he says he needs 1 week.

> Your alternative to my answer is simply where the OP started, with the
> exception that in my database it doesn't work, even for a week. And it
> is still more inefficient regardless of how many days involved, though
> in this case it is nearer to 15000 than 7.

Both points are well known solutions for this problem. Both have inherent flaws, one needs > 9i, the other needs some table (even an oracle system table) to select against. If you have oracle > 9i or happen to be running with no data in your database then this solution is probably not the correct one.

Seriously though, your solution is by far the more elegant. I provided the second as an alternative for pre 9i.

> --
Received on Wed Apr 27 2005 - 08:33:09 CDT

Original text of this message