--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...>
wrote:

>
> Muthu,
>
> >>Yes, but you're using a SUM function here.
> >>
> >>This is NOT the same as a plain select.
> >>The result of SUM itself is initialized with NULL, so the

coalesce

> >>works. ;-)
> >>
> >>
> >>Luc.
> >
> >
> > Hi,
> >
> > I still don't understand, I think I can catch after a while

working

> > with coalesce.
>
> The source of the problem is not COALESCE, it's to undesrtand the
> difference between NOTHING FOUND and NULL.
> NULL means NO [UNKNOWN] VALUE, which is totally different to "not

found".

>
> (Or do you expect a data grid wo show a row of <NULL>s when no

records

> are found?)
>
> in your first case you did a:
>
> SELECT COALESCE ( FIELD, 0 ) ...
> FROM ...
> INTO ...
> which resulted in NULLS since your select didn't return any row.
>
> In your second case you did:
>
> SELECT COALESCE(SUM(AMOUNT),0)
> ^^^
> FROM ...
> INTO ...
>
> which resulted in a 0 since:
> SUM is kind of a built-in aggregate function which will always

return

> NULL if no match is found (or any of the sumands is NULL) so your
> COALESCE will return 0 EVEN if no record is found.
>
> It's not that the SELECT isn't evaluated at all, it's just that

your

> AMOUNT expression will be initialized by using SUM thus COALESCE

will

> get a NULL "input parameter" instead of a "NOTHING" one.
>
> HTH
>
> Luc.

Thanks Luc

I hope I understood.

If there is no matching row then the variable is left untouched and
when using function like sum then it initialize the variable to null

Muthu