NZ function and Max wierdness with JET SQL

[Pink Panther]

Using the following SQL can the results be explained?

Using A97 (with the SP2 for Jet 3.5) or A2002

CREATE TABLE Test
(PK Number CONSTRAINT PK_TEST PRIMARY KEY, ParentID Number,
Child Number);

INSERT INTO Test VALUES(1,1,1);
INSERT INTO Test VALUES(2,1,2);
INSERT INTO Test VALUES(3,1,10);
'with an NZ wrapper
SELECT Max(nz([Child],0)) AS MaxChild
FROM Test
WHERE Test.ParentID = 1;

'without NZ Wrapper
SELECT Max([Child]) AS MaxChild
FROM Test
WHERE Test.ParentID = 1;

- with an NZ wrapper if the max is >=10, the value 2 will be returned by
the query engine.
The actual max will be returned without the nz function wrapping the
return value ie 10.

Now

UPDATE TEST SET Child = 3 WHERE PK = 3

- If the max value is <10 both SELECTs will return the same result ie 3.

Using SQL Server 2000 and

--with an ISNULL wrapper
SELECT Max(ISNULL([Child],0)) AS MaxChild
FROM Test
WHERE Test.ParentID = 1; both with and with ISNULL return the same result
ie 10, as I would expect.

What am I missing here? Can anyone explain the results sets and why nz
would cause a difference only if the max is >=10?

Peter

[Allen Browne]

The problem occurs because Nz() returns a variant, and JET misunderstands
this to be a string. The value "2" is returned because the string "2" is
greater than the string "10".

You can verify this is the problem with:
SELECT TypeName(Max(nz([Child],0))) AS MaxChild
FROM Test WHERE ParentID = 1;
The query returns the word "String".
If you copy your query into SQL View of the query design, and view the
result, you will see the 2 align left, indicating that Access interprets it
as a string value.

Your example is a very timely reminder that *any* calculated field (other
than string) should be explicitly typecast, i.e.:
SELECT CDbl(Nz(Max(Child),0)) AS MaxOfChild
FROM Test WHERE ParentID = 1;
For dates, CVDate() is very useful, as it also handles Nulls and may avoid
the need to use Nz().

(Note: the query I've suggested will return a negative value as the max,
whereas the one you posted may not: since you used Nz() before choosing the
max, the zero would be greater than all negative values.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Pink Panther" <pi*************@mail.com> wrote in message
news:3f********@news.iprimus.com.au...

Using the following SQL can the results be explained?

Using A97 (with the SP2 for Jet 3.5) or A2002

CREATE TABLE Test
(PK Number CONSTRAINT PK_TEST PRIMARY KEY, ParentID Number,
Child Number);

INSERT INTO Test VALUES(1,1,1);
INSERT INTO Test VALUES(2,1,2);
INSERT INTO Test VALUES(3,1,10);
'with an NZ wrapper
SELECT Max(nz([Child],0)) AS MaxChild
FROM Test
WHERE Test.ParentID = 1;

'without NZ Wrapper
SELECT Max([Child]) AS MaxChild
FROM Test
WHERE Test.ParentID = 1;

- with an NZ wrapper if the max is >=10, the value 2 will be returned by
the query engine.
The actual max will be returned without the nz function wrapping the
return value ie 10.

Now

UPDATE TEST SET Child = 3 WHERE PK = 3

- If the max value is <10 both SELECTs will return the same result ie 3.

Using SQL Server 2000 and

--with an ISNULL wrapper
SELECT Max(ISNULL([Child],0)) AS MaxChild
FROM Test
WHERE Test.ParentID = 1; both with and with ISNULL return the same result
ie 10, as I would expect.

What am I missing here? Can anyone explain the results sets and why nz
would cause a difference only if the max is >=10?

Peter

[Pink Panther]

Makes perfect sense (now).

Thanks Allen.

"Allen Browne" <ab***************@bigpond.net.au> wrote in
news:w%*******************@news-server.bigpond.net.au:

The problem occurs because Nz() returns a variant, and JET
misunderstands this to be a string. The value "2" is returned because
the string "2" is greater than the string "10".