Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]
Date: Wed, 8 Jun 2005 10:51:14 +0000 (UTC)
Message-ID: <Xns966F828284045jarl_at_62.127.77.84>
Paul <paul_at_test.com> wrote in
news:42a6c7bd$0$32618$ed2619ec_at_ptn-nntp-reader02.plus.net:
> I'd rather the SUM of a column containing a NULL return NULL. If I
> want to override the behaviour, I can always do a SUM(COALESCE(col)).
> But if the default is to treat NULLs as zero, I can't manually
> override it to the behaviour I want.
>
> At least MSSQL warns you that it's eliminated a NULL from an
> aggregate, although that's not much use if you're not running the
> query interactively.
Paul,
That's not really a MS SQL Server feature, its according to the ISO/ANSI SQL standard.
SQL-99:
6.16 <set function specification>
General Rules
1) Case:
a) If COUNT(*) is specified, then the result is the cardinality of T.
b) Otherwise, let TX be the single-column table that is the result of
applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning — null value eliminated in set function.
(SQLSTATE is set to 01003)
Regards,
Jarl
Received on Wed Jun 08 2005 - 12:51:14 CEST