From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: something simple but I can't |
Date: | 2011-01-29 03:46:37 |
Message-ID: | ii02it$dcm$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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' ;
--
⚂⚃ 100% natural
From | Date | Subject | |
---|---|---|---|
Next Message | John Fabiani | 2011-01-29 15:14:22 | Re: something simple but I can't |
Previous Message | John Fabiani | 2011-01-29 01:16:04 | something simple but I can't |