I've got this function that works great when I select from it from
dual:

select is_rejecting_fn(2408) from dual

returns 1

but If I attempt to use it in a where clause it apparently does not:

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

returns no rows:

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

returns many rows, including one with account_number = 2408


The function:


CREATE 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;


Thanks for any help or information.

Why another post for the same problem?
Check the other thread.
-----
échangez opinions et commentaires dans les forums de discussion.
http://www.usenetgratuit.com/