NZ function and Max wierdness with JET SQL

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

re: NZ function and Max wierdness with JET SQL

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" <pinkpanther2003@mail.com> wrote in message
news:3f570d68_1@news.iprimus.com.au...[color=blue]
> 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[/color]

re: NZ function and Max wierdness with JET SQL

Makes perfect sense (now).

Thanks Allen.

"Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in
news:w%E5b.82513$bo1.18498@news-server.bigpond.net.au:
[color=blue]
> 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".
>[/color]