simple sql question/mlml

From: Michel Lee <ae299_at_FreeNet.Carleton.CA>
Date: 1997/10/07
Message-ID: <61cdkk$b2i_at_freenet-news.carleton.ca>#1/1


[Quoted] Hi All,

[Quoted] [Quoted] I have this 'newbie' sql question , i hope some has encountered. (im pretty sure someone knows the answer)

I have 2 tables.
'fiscalyear' defines fiscal periods with date ranges.

   e.g.



| PER | BEGIN_DATE | END_DATE |
| 9701 | 10-JAN-97 | 13-FEB-97 |
| 9702 | 14-FEB-97 | 11-MAR-97 |
  ...
'transactions' are transactions with many dates.   e.g.

| AMT | DATE1 | DATE2 | DATE3 | DATE4 |
   ...

How do i display each transaction row with these dates 'converted' to their
fiscal periods?
my attempt is

select t.amt, d1.per, d2.per, d3.per, d4.per from

   transactions t,

   fiscalyear   d1,
   fiscalyear   d2,
   fiscalyear   d3,

   fiscalyear d4
where
[Quoted]    t.date1 between begin_date and end_date and
   t.date2 between begin_date and end_date and
   t.date3 between begin_date and end_date and
   t.date4 between begin_date and end_date ;


but this sounds very inefficient since its count a cartesion product with all the joins. (my report runs a very long time)

yr help is appreciated.

Mike

--
    /-----------------------------------------------/ \--\--\  \-- \-- \--
[Quoted] [Quoted]    /             www.ncf.carleton.ca/~ae299        /---\  \  \ \\   \   \
  /             ae299_at_freenet.carleton.ca         /-----\     \ \\-- \-- \--
 /-----------------------------------------------/
Received on Tue Oct 07 1997 - 00:00:00 CEST

Original text of this message