Hi

Thanx Michel ... you technique indeed works ...

Many thanx

/M

On Mon, May 17, 2010 at 9:10 AM, Michel Veerman <m.veerman@...>wrote:

>
>
> 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 <firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>]
> Namens moegamata
> Verzonden: maandag 17 mei 2010 8:51
> Aan: firebird-support@yahoogroups.com <firebird-support%40yahoogroups.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]
>
>
>


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