Re: simple sql question/mlml

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


Hi John,

Thank you very much for your input.
Do you think that creating a new fiscalyear table with 2 columns. 1st col=date (index here)
2nd col=fiscalmonth
so that each day has 1 row,
will be faster?
This way there is no need for the 'between' but the '=' logic.

Mike

Jomarlen (jomarlen_at_aol.com) writes:
> Hi Michel
>
> Unfortunately, whatever you do you are going
> to have to query table fiscalyear once for each
> transaction date. I think the most efficient way
> to achieve this is by using a stored function:
>
> Create or Replace
> FUNCTION Fiscal_Period(p_Date DATE)
> RETURN VARCHAR2
> IS
> Return_Value fiscalyear.per%TYPE;
> CURSOR Per_Cur IS
> SELECT per
> FROM fiscalyear
> WHERE p_Date BETWEEN begin_date
> AND end_date;
> BEGIN
> OPEN Per_Cur;
> FETCH Per_Cur
> INTO Return_Value;
> CLOSE Per_Cur;
> RETURN Return_Value;
> END;
>
> Your query will now look like:
>
> SELECT amt
> ,fiscal_period(date1)
> ,fiscal_period(date2)
> ,fiscal_period(date3)
> ,fiscal_period(date4)
> FROM transactions;
>
> If this gains you nothing else it
> reads better andd looks simpler.
> Also, presumably you'll have
> conditions in a where clause and
> these will be evaluated more quickly
> without all the joins.
>
> You might find that changing
> the cursor in the function to:
>
> SELECT per
> FROM fiscalyear
> WHERE begin_date <= p_Date
> AND end_date >= p_date;
>
> is more efficient (indexes on the date cols?).
>
> Note that I haven't used an EXCEPTION in the
> function. I've found that, for functions
> embedded in SQL it's better to let the
> SQL engine report errors just like any other
> error (like table or view does not exist).
> Exception trapping just serves to mask errors.
>
> If you want to check out other ways to use functions
> try down loading my Oracle Informant magazine article
> *Functional Magic* from the Oracle Utilities page on
> my web site. (http://members.aol.com/jomarlen).
>
> Hope this helps.
> John
> -------------------------------------------------------
> John C. Lennon
> Utility Partners Inc.
> 4300 West Tropicana Blvd LVO-UPL
> Las Vegas NV 89103
> e-mail: jomarlen_at_aol.com
> Web Site: http://members.aol.com/jomarlen/
>
> The views expressed in this message
> are those of the author and not
> necessarily those of U.P. Inc.
> and/or its employees.
> -------------------------------------------------------
>
>
>

>> Michel Lee wrote:
>>> 
>>> 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 Mon Oct 13 1997 - 00:00:00 CEST

Original text of this message