I've got this function that tells me when a customer's last few orders
had rejected credit cards transactions in our orders table.

When I run the fuction against dual manually plugging in the account
number which is the only parameter to the function it returns a 1
telling me the customer qualifies. However, if I include the function
in a select where plugging in the account number from the select, it
never finds any rows.

Thanks for any help or information.


my function:

REATE OR REPLACE FUNCTION IS_REJECTING_Fn(iaccount_number in number)
RETURN number

AS
bad_count number;
vlast_update date;

begin
select count(*) into bad_count from (select * from (select * from
orders o where o.ACCOUNT_NUMBER=iaccount_number order by
o.VALIDATION_DATE desc)
where rownum <4) where status=-20 ;

select last_update into vlast_update from
autorecharge a where a.account_number=iaccount_number;

if (bad_count=3) and (vlast_update<sysdate-3 or vlast_update is null)
then
return 1;
else
return 0;
end if;

END Is_Rejecting_fn;


It returns a 1 here:

select is_rejecting_fn(2408) from dual

this code pulls many rows including one with account_number 2408

select * from orders o where trunc(o.validation_date) > '14-Oct-2007'
and status=-20

This pulls nothing:

select * from orders o where trunc(o.validation_date) > '14-Oct-2007'
and status=-20 and
is_rejecting_fn(o.account_number)=1

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?
-----
échangez opinions et commentaires dans les forums de discussion.
http://www.usenetgratuit.com/

On Oct 16, 9:05 am, vitalis... (AT) gmail (DOT) com (Jerome Vitalis) wrote:
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.