Re: Examples of SQL anomalies?
Date: Fri, 27 Jun 2008 10:00:18 -0700
Message-ID: <bv6a649et7clrgaipvb83kfime8tcblmmi_at_4ax.com>
Philipp Post <Post.Philipp_at_googlemail.com> wrote:
>> Others have mentioned nulls. One of my pet peeves in this area
>is that in SQL, if I sum zero numbers, the result is null.
>Mathematically, the result is 0. I have an app where I have had make
>adjustments where it is possible that zero numbers are summed. <
>
>SQL Server at least stripps off NULLs in the SUM function and returns
>a number, but not in a simple addition - then it returns NULL if at
>least one entry is NULL.
>
>> SUM(A) + SUM(B) != SUM(A+B) <
>
>The question is, if these issues are due to the SQL specification or
>simply due to a problem in a specific SQL product. Or could it be,
I am told that it is the spec.
>that the definition is not precise enough in some points, so that
>database vendors implemented it differently? A quick look at the SQL
>92 spec did not sched much light on this for me regarding the source
>of the problem.
I do not use nulls in my tables. I still get null when I sum what turns out to be zero rows. If I want the total amount charged on invoices for a client in a certain date range, and there are no such invoices, the total is zero. SQL returns null. Every place where that possibility exists, I have to patch the cursor.
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences. You have biases. He/She has prejudices.Received on Fri Jun 27 2008 - 19:00:18 CEST