Moegamat Alexander wrote:

> Hi
>
> It seems by adding another coalesce seems to do the trick ... question is
> why
>
> Sub query
>
> coalesce((select *coalesce(a.CreditSales,0)*
> FROM crdsales_v a
> where a.salesdate = z.salesdate),0) as CreditSales,

For the reason already stated. The sub-select will not return anything if there
are no records to return, so the sub-select is null, not the 'value returned'.
You wanted 'if nothing returned ... then 0' which is the outer coalesce. The
INNER one is probably redundant?

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php