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.