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

TableA

> > > >
> > > > But this is not working. I tried to use IF..THEN..ELSE But it was

also

> > > failure for me.
> > >
> > > In Firebird 1.5 you can do:
> > >
> > > SELECT
> > > Code,
> > > CASE WHEN SUM(Amount) IS NULL THEN 0 ELSE Amount END
> > > FROM
> > > TableA
> >
> > Mind you - the result of the above query is different that what might
> > be the intention of the first attempt.
> >
> > You're transforming SUM = NULL to 0, while his first attempt was
> > to SUM x, but when x = NULL, then x = 0...
>
> Yes, but it is more efficient to do it after the SUM else is for every

value

> also the CASE/COALESCE evaluated and in my example only afterwards. The

SUM

> doesn't sum NULL values, they are already ignored, but the result off SUM
> can be NULL :-)

Hey Arno -- you are right, NULLs are filtered out of the sum ... I stand
corrected :-)


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com