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: Creating temp table for dates

Re: Creating temp table for dates

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 6 Oct 2004 23:23:49 GMT
Message-ID: <slrncm8vjo.gg.rene.nyffenegger@zhnt60m34.netarchitects.com>


> Hi,
>
> We have a bit of a problem and maybe someone knows a good
> solution.....we are thinking of using a temp table in oracle.
>
> We currently List a set of Data for any given week in a report. Say
> for instance the date parameter passed to the report is "7/10/04" the
> Output should look as follows:
>
> Date Qty
> *****************************
> Mon 04/10/2004 2
> Tue 05/10/2004
> Wed 06/10/2004 3
> Thu 07/10/2004 7
> Fri 08/10/2004
> Sat 09/10/2004
> sun 10/10/2004
>
> We need an entry for every day of that week (starting mon) even for
> days that have no entries in the database.
>
> WE ARE NOT ALLOWED TO CREATE ANOTHER TABLE IN THE DATABASE
>
> We are contemplating using an Oracle stored procedure and generating a
> temp table that has all the dates for that week and then performing a
> RIGHT join with it on the data.
>
> I see people have said do not create a temp table.....is there a
> better solution for our problem???

Don't know if you consider this approach better, but try:

alter session set nls_date_format = 'dd/mm/yyyy';

select start_day+offset from
(select trunc(to_date('07/10/2004'), 'D') start_day  from dual)
cross join
(select rownum offset from all_objects
 where rownum < 8);

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/
Received on Wed Oct 06 2004 - 18:23:49 CDT

Original text of this message

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