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