On 3 Nov 2003 at 16:51, Tanz Anthrox wrote:

> I have problems with NULL fields.
>
> SELECT Code, Sum(Amount) from TableA
>
> the problem if the AMOUNT is NULL then SUM field is NULL. But I need the Value ZERO (0)
>
> I tried to format it
>
> SELECT Code, SUM(CASE Amount WHEN NULL THEN 0 else Amount) from TableA

Well, the proper test for null is with IS or IS NOT NULL, so it should
look like

SELECT Code, SUM(CASE Amount WHEN IS NULL THEN 0 else Amount) from TableA

But you should rather use COALESCE in this case :-)

COALESCE allows a column value to be calculated by a number of
expressions, from which the first expression to return a non-NULL value
is returned as the output value.

In your example it would be:

SELECT Code, SUM(COALESCE(Amount,0)) from TableA

Best regards
Pavel Cisar (ICQ: 89017288)
http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information