Null issue 1

[simian336]

The following query results in error

Warning: Null value is eliminated by an aggregate or other SET operation.

(6 row(s) affected)

However if I only select not an insert it does not give the error but either way I get the correct 6 results?

Anybody have any idea what might throw that error?



With #Temp As(
Select rpid, ttid,
Count(Case When info3 is not null Then 1 End) As CountInfo3,
Min(Case When Info3 is not null Then TSeid End) As MinTseidCount1,
Min(tseid) As MinTSeid
From #UsageTemp
Group By rpid, ttid
),
finalusage as
(
Select T.*
From #Temp
Inner Join #UsageTemp T
On T.TSeid = Case
When CountInfo3 = 1
Then MinTseidCount1
Else MinTSeid
End
)
insert into [TempWil].[dbo].[usage2]
select * from finalusage


Thanks

Simi

 

[gmmastros]

You can suppress that warning message like this:

[!]SET ANSI_WARNINGS OFF[/!]

[!];[/!]With #Temp As(
    Select rpid, ttid,      
        Count(Case When info3 is not null Then 1 End) As CountInfo3,
        Min(Case When Info3 is not null Then TSeid End) As MinTseidCount1,
        Min(tseid) As MinTSeid  
    From #UsageTemp  
    Group By rpid, ttid
),
finalusage as
(
Select T.* 
From #Temp
Inner Join #UsageTemp T           
    On T.TSeid = Case 
When CountInfo3 = 1 
    Then MinTseidCount1 
    Else MinTSeid 
End
)
insert into [TempWil].[dbo].[usage2] 
select * from finalusage

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

By the way....

Count will only count the non-null values, so you can change this:

[tt]Count(Case When info3 is not null Then 1 End) As CountInfo3[/tt]

To:

[tt]Count(info3) As CountInfo3[/tt]

Proof:

Declare @Temp Table(info3 Int)

Insert into @Temp Values(1)
Insert into @Temp Values(2)
Insert into @Temp Values(3)
Insert into @Temp Values(NULL)
Insert into @Temp Values(NULL)

Select Count(*) As AllCount,
       Count(info3) As CountInfo3NotNull,
       Count(*) - Count(info3) As CountInfo3Null
From   @Temp

Count(*) includes nulls. Count(ColumnName) ignores nulls.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

Thanks for the info.

Any idea what condition causes that warning?

Thanks

Simi

 

[gmmastros]

Sure. It's caused by using the count function on a column where the value in the column is null. Ex:

Declare @Temp Table(id int, Val int)

Insert into @temp values(1,1)
Insert into @temp values(2,null)

Select id, count(val)
From   @Temp
Group By Id

If you run the following code in a query window, you'll see 2 rows in the output. Now, if you click on the message tab, you'll see:

[tt][blue]

(1 row(s) affected)

(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)
[/blue][/tt]

The easiest way to accommodate this problem is to use the SET ANSI_WARNINGS OFF directive, like I already advised. Another way would be to use the sum function instead of count. Ordinarily, sum will add the values stored in a column, but if you add 1 for each value (instead of the actual value) you will get the same output as the count function.

Ex:

Declare @Temp Table(id int, Val int)

Insert into @temp values(1,1)
Insert into @temp values(2,null)

Select id, sum([!]case when val Is NULL then 0 else 1 end[/!])
From   @Temp
Group by id

You get the same result, but no warning message. In this situation, there is always a value to sum. It's either 1 or 0, but never null.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

George,

I want to be like you when I grow up. : )

Thanks

Simi