Re: function refuses to work in where clause??

On Oct 16, 9:05 am, vitalis..._at_gmail.com (Jerome Vitalis) wrote:
> There's nothing obviously wrong here. Have you tried some simpler tests
> like:
>
> select account_number,is_rejecting_fn(account_number) from orders;
> and
> select account_number,1 from orders where
> is_rejecting_fn(account_number)=1;
>
> ?
>
> Are the results correct?

Agreed, nothing leaps out at me here, although an obvious optimisation would be to skip the autorecharge lookup if bad_count <> 3, because you're going to return 0 anyway.

I would want to see what the function returns in the query that returns rows

select is_rejecting_fn(o.account_number), o.* from orders o
where o.validation_date >= DATE '2007-10-15' and status = -20;

etc

I'd also be double-checking the contents of autorecharge, and considering running the query through a debugger or tkprof. Received on Sun Oct 21 2007 - 04:06:13 CDT