Re: Set a warning on null values.

Matt Piskorz wrote:
> I'm having an issue with a query where I am getting back an extra
> record.  

I doubt that.  I don't know what you're seeing because you described it
rather than showing it, but the server surely returned only one row. 
That's what the SQL max() function requires of it.  

> I receive this warning when I run the query in the ms sql client tool -
> "Warning: Null value is eliminated by an aggregate or other SET
> operation."

That's a message from the server.  If you run your query in tsql, you'll
see the same message.  The unixODBC tool often doesn't print error/warning
messages.  (Whether or not the server produces the message is controlled
by the ANSI_WARNINGS session variable.  Is is on by default for ODBC
connections.)  

> If I run the query as is in unixodbc isql, then I get the 4th row.

I suspect a problem with the tool.  FreeTDS includes a variety of
utilities.  If any of them produces a "4th row", please do show.  

I ran the following tests.  I got a variety of outcomes, some of which
need attention, but none of which evince an extra row.  (fisql prints no
message; bsqlodbc is fussy about what's in a batch.) 

$ cat row4.pr.sql; for A in tsql bsqldb fisql bsqlodbc; do printf "\n#\n#
$A\n#\n"; $A -S $S -U $U -P $P < row4.pr.sql; done

create table test(c1 int null, c2 int null) 
insert into test values( 1,1 )
insert into test values( 1,2 )
insert into test values( 1,null )
go

set ANSI_WARNINGS ON
select max(case when c1 = 1 then c2 end) as 'case 1'
from test  --this returns 2 rows
go
select max(case when c1 = 1 then c2 end) as 'case 2'
from test where c2 is not null -- this returns 1 row
go

drop table test
go

#
# tsql
#
locale is "C"
locale charset is "646"
Msg 8153, Level 0, State 1, Server TITAN, Line 3
Warning: Null value is eliminated by an aggregate or other SET operation.
locale is "C"
locale charset is "646"
1> 2> 3> 4> 5> 1> 2> 3> 4> 5> case 1
2
(1 row affected)
1> 2> 3> case 2
2
(1 row affected)
1> 2> 3> 1> 
#
# bsqldb
#
@@rowcount not available
@@rowcount not available
     case 1
-----------
Msg 8153, Level 0, State 1
Server 'TITAN', Line 3
        Warning: Null value is eliminated by an aggregate or other SET
operation