Re: fuction using view with union stops query

I'm on 9i. I built this entire mock up to try and replicate in small scale, but could not get it to fail.

Basically I've zero in on that If remove one half of the union (in the view), that the function that uses, the function works fine in the select (but of course misses many of the rows I want..

the function works fine either way on a select from dual and when none of the from columns are used to drive it in a select.

the view (when no rows come back from the select using the function) create or replace view vw_mcmlinea_combined as select m.account_number,m.cdani,m.creation xdate from mcmlinea m  union select h.account_number,h.cdani,h.dtfecestado_end xdate from mcmlinea_hst h

the view (when the function works but is missing data): create or replace view vw_mcmlinea_combined as select m.account_number,m.cdani,m.creation xdate from mcmlinea m  --union select h.account_number,h.cdani,h.dtfecestado_end xdate from mcmlinea_hst h

the function:

CREATE OR REPLACE FUNCTION GETANI_FN (iaccount_number in number) return varchar2 is

  ani varchar2(20);
begin
 if iaccount_number >0 then
 begin
  SELECT mm.cdani into ani FROM vw_mcmlinea_combined mm where mm.account_number=iaccount_number

     and mm.xdate =
  (select max(xdate) from vw_mcmlinea_combined where account_number = mm.account_number);
  return ani;
  end;
  end if;
  return '';
end GETANI_FN;

Thanks for any help or infomation. Received on Sat Sep 15 2007 - 07:40:12 CDT