On Friday, January 28, 2011 07:46:37 pm Jasen Betts wrote: > On 2011-01-29, John Fabiani <[email protected]> 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
