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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to generate the output w/o using temp table

RE: how to generate the output w/o using temp table

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 2 Aug 2004 16:28:40 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKKECPFCAA.mwf@rsiz.com>


First, I'm not sure why 10/2004 1 is not in the result set, and I think you're trying to deliver an inclusive list of months corresponding to each start and end date.

Where you have to generate time based lists, I recommend reference tables. These are not temporary, but permanent reference lists to join against. Usually you keep a moving window guaranteed by an affirmative business rule to exceed the endpoints by a healthy margin.

So you need, for example, a months table that is simply a date column filled in. A hundred years worth would be 1200 rows, so not big. Probably a good idea to index it. Even days is not very big, and years is really tiny. Weeks and quarters can be problematic, as can fiscal years, since you have to insert values based on business defined boundaries rather than the natural calendar functions.

For natural calendar functions, you just need the single date value, truncated on creation to the value represented.

select a.id, to_char(b.mymonths,'MM/YYYY') from a, b where b.mymonths between a.startdate and b.enddate;

Now if you have artificial date groupings, you need the name, starttime, and endtime (let's say mymonthname, mymonthstart, and mymonthend). Some people, for example, might end this year's DECEMBER on December 25, if they only include full Sunday through Saturday weeks within the year.

select a.id, b.mymonthname from a,b
where b.mymonthstart >= a.startdate
  and b.mymonthend <= a.enddate;

I recommend having all the distinct reference time tables you need to enumerate by (rather than combining them and having a type column which just confuses all sorts of issues), but possibly the books Lex mentioned have other solutions. As far as I know, I invented the method mentioned here, but probably other folks have also invented it. It is fast and small for reasonable time quanta. If you start wanting a list of inclusive microseconds, then you need a different solution unless you have a very, very small time window.

If you read those books mentioned, please let me know if they have a better solution.

regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of mglim2_at_softhome.net Sent: Saturday, July 31, 2004 5:39 AM
To: oracle-l
Subject: how to generate the output w/o using temp table

Hi,

I got a table w/ the ff: data

id startdate enddate

1   10/01/2004  12/13/2004
2   12/01/2004  12/02/2005
3   12/03/2004  02/20/2005


need to produce the ff:output

mm/yyy            id
11/2004            1
12/2004            1
12/2004            2
12/2004            3
01/2005            3
02/2005            3

how can i produce this result w/o using a temporary table in my select stmt?

Best Regards,
Grace Lim
Suy Sing Comm'l Corp



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Mon Aug 02 2004 - 15:29:04 CDT

Original text of this message

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