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
| 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 |
...
fiscalyear d4
where
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