From: | John Fabiani <johnf(at)jfcomputer(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: something simple but I can't |
Date: | 2011-01-29 15:14:22 |
Message-ID: | 201101290714.22366.johnf@jfcomputer.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Friday, January 28, 2011 07:46:37 pm Jasen Betts wrote:
> On 2011-01-29, John Fabiani <johnf(at)jfcomputer(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Gaab | 2011-01-31 08:52:23 | Re: aggregation of setof |
Previous Message | Jasen Betts | 2011-01-29 03:46:37 | Re: something simple but I can't |