Re: fuction using view with union stops query

From: <fitzjarrell_at_cox.net>
Date: Mon, 17 Sep 2007 07:07:57 -0700
Message-ID: <1190038077.880303.35780@o80g2000hse.googlegroups.com>

On Sep 15, 7:40 am, jobs <j..._at_webdos.com> wrote:
> 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.

Why are you returning NULL (the empty string) unconditionally? Your function should recognize that you have two possible conditions and process appropriately:

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

   ani                   varchar2(20);

begin
  if iaccount_number >0 then
   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;
  else
   return '';
  end if;
end GETANI_FN;
/

I'd return something other than NULL for account numbers less than or equal to 0, but that's your choice, I expect.

David Fitzjarrell Received on Mon Sep 17 2007 - 09:07:57 CDT