Nando's suggestion holds in the case of aggregates as well. The idea would be to say

aggregate(expression, out_of_band)

Basically, it's implied currently that you're doing

aggregate(expression, NULL)

But if you want to talk about aggregates and NULL, don't forget that

"1 + NULL" is NULL

but in the case of

a
-----
NULL
1

"sum(a)" is 1

Also suggested (theory-wise) would be outer joins that specify immediately what out-of-band value
(rather than state) to use if no information can be found ... rather than COALESCE, which assumes
you get NULL first, -then- change it, you actually make the join do it as it goes, without a NULL
actually in sight (and impossible to get?) I could imagine you'd specify it in the SELECT fieldlist
clause though, rather than the join ... same as COALESCE. (And if you were getting rid of NULL
entirely, it might look exactly the same, as COALESCE wouldn't have another meaning.)

-Philip

---------- Original Message -----------
From: "Ivan Prenosil" <Ivan.Prenosil@...>
To: <firebird-support@yahoogroups.com>
Sent: Fri, 28 May 2004 16:50:53 +0200
Subject: Re: Re[2]: [firebird-support] NULL and equality

> > > You can build a perfect relational database without needing to even
> > > know that null exists.
> >
> > ... unless you use outer joins, that can return null even for not null columns :-)
>
> ... or aggregates.
>
> Ivan
>
> ------------------------ Yahoo! Groups Sponsor --------------------~-->
> Yahoo! Domains - Claim yours for only $14.70
> http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/67folB/TM
> --------------------------------------------------------------------~->
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------