Thread: Cast question (NULL -> NUMERIC)

I have the following query:

    SELECT cast( sum(amount) AS NUMERIC(9,2) ) ...
    WHERE postdate < $startdate

(where $startdate is replaced with the user's entry at runtime) which returns
the balance forward of the amount column for the user-suplied date correcly
unless the user supplies a $startdate such that no culumns are being added.
In this case NULL is returned. I want to receive 0.00 in this case instead of
NULL, so I added the cast above, but NULL is still returned. Any ideas?

--
Henry House
OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc

Henry House <hajhouse@houseag.com> writes:
> the balance forward of the amount column for the user-suplied date correcly
> unless the user supplies a $startdate such that no culumns are being added.
> In this case NULL is returned.

Yeah, for some unfathomable reason SQL92 defines SUM() of no rows to
return NULL, rather than zero as any mathematician would say it should.

Use COALESCE(SUM(...), 0) to replace the null result by 0.

            regards, tom lane

On Fri, Jul 13, 2001 at 05:11:25PM -0400, Tom Lane wrote:
[..]
> Yeah, for some unfathomable reason SQL92 defines SUM() of no rows to
> return NULL, rather than zero as any mathematician would say it should.
>
> Use COALESCE(SUM(...), 0) to replace the null result by 0.
>
>             regards, tom lane

That did the trick. Thanks!

--
Henry House
OpenPGP key available from http://romana.hajhouse.org/hajhouse.asc