You probably want your coalesce to be around the whole subselect...
e.g.:



coalesce ((select a.CreditSales
FROM crdsales_v a
where a.salesdate = z.salesdate), 0) as CreditSales,



If you don't, and the subselect returns no records, then "CreditSales"
will indeed be "null".



Kind regards,

Michel Veerman





________________________________

Van: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Namens moegamata
Verzonden: maandag 17 mei 2010 8:51
Aan: firebird-support@yahoogroups.com
Onderwerp: [firebird-support] Sub query returns NULL





Hi

I have a problem in that my subquery returns a null value despite the
fact that I have it wrapped in a coalesce ... What am I missing?

Many thanx

/M

SELECT
z.salesdate,
sum(w.EXTENDED) SalesTotal,
sum(w.DISCOUNT) Discounts,

(select a.roundloss
FROM round_v a
where a.salesdate = z.salesdate) as RoundLoss,

(select coalesce(a.CreditSales,0)
FROM crdsales_v a
where a.salesdate = z.salesdate) as CreditSales,

..
..
FROM asales z,sales v,salesitems w,salestype x
where z.salesdate between :sdate and :edate
..
..
group by z.salesdate





[Non-text portions of this message have been removed]