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: simple sql question/mlml

Re: simple sql question/mlml

From: Gerard H. Pille <ghp_at_santens.be>
Date: 1997/10/07
Message-ID: <01bcd30b$e94a6880$7b1340c0@pcghp.santens.be>#1/1

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>...

>
>
> Hi All,
>
> 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 /-----\ \ \\-- \--
\--
> /-----------------------------------------------/
>
Received on Tue Oct 07 1997 - 00:00:00 CDT

Original text of this message

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