Re: Sql Question

Half correct.

Comparisons to NULL do result in NULL, which is neither TRUE or FALSE.
Thus the reason the rows are not being returned as expected.

However, aggregate functions skip NULL. The manual puts it like so,
"The function is applied to the set of values derived from the
argument values by the elimination of null values."

So AVG() over FLD1 in the following:
KEY1 FLD1
1 2
2 2
3 3
4 NULL
5 5

Is 3, not 2.4.

AVG({2,2,3,NULL,5}) = AVG({2,2,3,5}) = 3

HTH,
Charles




On Tue, Aug 17, 2010 at 10:05 PM, Loyd Goodbar <loyd@xxxxxxxxxxxxxx> wrote:

Let me guess: comparisons to NULL result in NULL. Also aggregate operations containing NULL result in NULL. --Loyd

On Aug 17, 2010, at 19:04, Alan Campin <alan0307d@xxxxxxxxx> wrote:

As the man said, "Very interesting" . Thanks

On Tue, Aug 17, 2010 at 5:40 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx>wrote:

Dang. here I sit, corrected.  :)  It was about time, since my last prior
correction was over ten minutes ago.

From Wikipedia: http://en.wikipedia.org/wiki/Null_%28SQL%29

SELECT *
FROM sometable
WHERE num <> 1;  -- Rows where num is NULL will not be returned,
               -- contrary to many users' expectations.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.