Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: table containing the days for a week

From: yves ludwig <yve_at_free.fr>
Date: Sun, 24 Apr 2005 21:39:57 +0200
Message-ID: <kCSae.49803$Of5.32055@nntpserver.swip.net>


Thanks for your answer,

Concerning (1), I understand your question

Concerning (2) it is yet clear for me how to work without a workaround table if I wish for example to make a weekly report containing Sales for all the days
 (based on a mySales and myCalendar table).

Currently I select all the days of the week in myCalendar table and seek for the sales total in the mySales table and obtain the following result : day sales


Monday         10
Tuesday         10
Wednesday    20
Thursday          0     // We were closed //
Friday            15
Saturday        50
Sunday           0


Without the myCalendar artifice, I only have the following result
day               sales
-------------------
Monday         10
Tuesday         10
Wednesday    20
Friday            15
Saturday        50

Which is a table that is not perfect for my reports...

//Unfortunately, This great Oracle magazine in which this topic was discussed does really miss me (I've checked both at my office & at home...)//

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> a écrit dans le message de news: 33mj61dsuv7g8afhgtqks23ck4kvbsu25k_at_4ax.com...
> On Sat, 23 Apr 2005 04:36:44 +0200, "yves ludwig" <yve_at_free.fr> wrote:
>
>>In one of my application, I need to have a table containing all the days
>>of
>>a week.
>>
>>Therefore, I created a table myCalendar containing one row / day for the
>>last 10 years and the next 30 years and perform the following select
>>
>> SELECT myDate from myCalendar where TO_CHAR(myDate,"IW")="16" and
>>TO_CHAR(myDate,"IYYY")="2005"
>>
>>that returns
>>
>> 18/04/2005
>> 19/04/2005
>> ...
>> 24/04/2005
>>
>>
>>I have two questions
>>
>>(1) How could this select be improved ?
>> I think that Oracle (or any DBMS) needs to generate the strings for
>> my
>>40 years and search the "16" and "2005" values.
>>In my application, I currently have added in the myCalendar table two
>>columns : myYear and myWeek containing the numeric values that are
>>calculated only one time : during the creation of the table. Therefore my
>>select is currently
>>SELECT myDate from myCalendar where myWeek=16 and myYear=2005
>>
>>(2) Is it possible to work without any 'myCalendar' table to obtain the
>>result ?
>>
>>
>>I remember I read an article concerning this topic in an old Oracle
>>Magazine
>>a few years ago (during winter 2002 or 2001)... {one should really keep
>>everything).
>>
>>
>>
>>
>>Thanks for help & best regards, Yves
>>
>>
> 1 Put a function based index on mydate
> create index .... on mycalendar(to_char(mydate,'IYYYIW')
>
> make sure init.ora query_rewrite_enabled=true and
> query_rewrite_trusted (from memory) = enforced
>
> 2 Do away with the workaround, and put function based indexes on the
> affected tables directly.
> You don't NEED the auxiliary table.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sun Apr 24 2005 - 14:39:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US