Re: Function Question

You may find variations in different versions of Oracle. For one thing the calculation will only take place for rows meeting the 'date-field' predicate. For another, with 8.1 you could declare the function to be deterministic.

You might also try:-

    select distinct id_field, date_field, calc_field from     (
    Select ID_Field, Date_Field, FOO(ID_Field, Date_Field) calc_field     From Table
    Where Date_Field > To_Date('01-JAN-99', 'DD-MON-YY')     )
    AND calc_field > 0;

One option to check what's happening is to put a tell-tale SQL in the function, and turn on SQL_TRACE.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Moore wrote in message ...
>Greetings!
>
>In the following will function foo be called twice per tupple?
>
>Select Distinct ID_Field, Date_Field, FOO(ID_Field, Date_Field)
>From Table
>Where Date_Field > To_Date('01-JAN-99', 'DD-MON-YY')
>AND FOO(ID_Field, Date_Field) > 0;
>
>By the way I already attempted:
>
>Select Distinct ID_Field, Date_Field, FOO(ID_Field, Date_Field) Foo_Field
>From Table
>Where Date_Field > To_Date('01-JAN-99', 'DD-MON-YY')
>AND Foo_Field > 0;
>
>Also attempted AS Foo_Field and "Foo Field".
>
>TIA
>
Received on Mon Mar 13 2000 - 14:06:27 CST