Обсуждение: something simple but I can't
Hi guys, I trying to return a 0.00 from a function it there are no records found else return the amount. create or replace function danmeans_getpayments(text) returns numeric as $BODY$ declare invoice_num ALIAS FOR $1; _paidamt numeric; BEGIN select sum(aropen_paid) into _paidamt FROM public.aropen where aropen_applyto is not null and (aropen_applyto = $1) ; IF (FOUND) THEN RETURN _paidamt ;END IF; RETURN 0.00 ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 But all I get is either a blank or the amount paid. What am I doing wrong??? Johnf
On 2011-01-29, John Fabiani <johnf@jfcomputer.com> wrote: > Hi guys, > I trying to return a 0.00 from a function it there are no records found else > return the amount. > select sum(aropen_paid) into _paidamt FROM public.aropen where > aropen_applyto is not null and (aropen_applyto = $1) ; > > IF (FOUND) THEN > RETURN _paidamt ; > END IF; > > RETURN 0.00 ; > But all I get is either a blank or the amount paid. What am I doing wrong??? > Johnf how many rows does the query return when no rows match the where?It returns 1 that looks like ( NULL ).it return 1 row,which is more than zero thus FOUND is TRUE. you can fix your function by changing the IF to IF _paidamt IS NOT NULL but if you change the sum to coalesce(sum(aropen_paid),0.00) you can do the task more simply like this: create or replace function danmeans_getpayments(text)returns numeric as $BODY$ select coalesce(sum(aropen_paid),0.00) FROMpublic.aropen where aropen_applyto is not null and (aropen_applyto = $1) ;$BODY$ LANGUAGE 'sql' ; -- ⚂⚃ 100% natural
On Friday, January 28, 2011 07:46:37 pm Jasen Betts wrote: > On 2011-01-29, John Fabiani <johnf@jfcomputer.com> wrote: > > Hi guys, > > I trying to return a 0.00 from a function it there are no records found > > else return the amount. > > > > select sum(aropen_paid) into _paidamt FROM public.aropen where > > > > aropen_applyto is not null and (aropen_applyto = $1) ; > > > > IF (FOUND) THEN > > > > RETURN _paidamt ; > > > > END IF; > > > > RETURN 0.00 ; > > > > But all I get is either a blank or the amount paid. What am I doing > > wrong??? Johnf > > how many rows does the query return when no rows match the where? > It returns 1 that looks like ( NULL ). > it return 1 row, which is more than zero thus FOUND is TRUE. > > you can fix your function by changing the IF to > > IF _paidamt IS NOT NULL > > > but if you change the sum to > > coalesce(sum(aropen_paid),0.00) > > you can do the task more simply like this: > > create or replace function danmeans_getpayments(text) > returns numeric as > $BODY$ > select coalesce(sum(aropen_paid),0.00) FROM public.aropen where > aropen_applyto is not null and (aropen_applyto = $1) ; > $BODY$ > LANGUAGE 'sql' ; Thanks to all who replied. All of your suggestions would work and it should have been obvious but it wasn't. Johnf