Re: simple sql question/mlml

Hi

I am sorry i put in the wrong sql script. But how can i modify this script to perform the desired function in a more efficeient way with these joins? i.e. display the different periods in the select statement?

cheers

Mike

"Gerard H. Pille" (ghp_at_santens.be) writes:
> select t.amt, d1.per, d2.per, d3.per, d4.per
> from
> transactions t,
> fiscalyear d1,
> fiscalyear d2,
> fiscalyear d3,
> fiscalyear d4
> where
> t.date1 between d1.begin_date and d1.end_date and
> t.date2 between d2.begin_date and d2.end_date and
> t.date3 between d3.begin_date and d3.end_date and
> t.date4 between d4.begin_date and d4.end_date ;
>
> --
> ------------
> Kind reGards
> \ / |
> X |
> / \ x
> Gerard
>
> Michel Lee <ae299_at_FreeNet.Carleton.CA> schreef in artikel
> <61cdkk$b2i_at_freenet-news.carleton.ca>...

>> 
>> 
[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
>>    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
>> 
>> 
>> 
>> --
>>     /-----------------------------------------------/ \--\--\  \-- \--
 \--
>>    /             www.ncf.carleton.ca/~ae299        /---\  \  \ \\   \   \
>>   /             ae299_at_freenet.carleton.ca         /-----\     \ \\-- \--

>>  /-----------------------------------------------/

--
    /-----------------------------------------------/ \--\--\  \-- \-- \--
   /             www.ncf.carleton.ca/~ae299        /---\  \  \ \\   \   \
  /             ae299_at_freenet.carleton.ca         /-----\     \ \\-- \-- \--
 /-----------------------------------------------/