We help IT Professionals succeed at work.

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

Medium Priority
1,314 Views
Last Modified: 2012-05-06
I have the following Query:

select '           ' + noName + ' ' + max(netObject.noType) + ' ' +
case when max(netObject.noIPAddr) IS NULL then '' else max(netObject.noIPAddr) END +
case when max(netObject.noIPAddr_First) IS NULL then '' else max(netObject.noIPAddr_Last) END + ' ' +
case when max(netObject.noNetmask) IS NULL then '' else max(netObject.noNetmask) END + ' ' +
case when max(netObject.noIPAddr_Last) IS NULL then '' else max(netObject.noIPAddr_Last) END
from destination join netObject on destination.noPK = netObject.noPK where destination.rPK = @rulePK group by noName

Which appears to work but gives me the following warning:

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

Perhaps there is a better way to do this anyway?  I wanted to use the FIRST() function like in Access, but SQL dosn't have it so I used MAX which seemed to work.

The values that I am MAX() are all strings, and they will always be the same in the query for each noName (noName is unique).

Thanks.

-Rowan
Comment
Watch Question

Commented:
try this

select '           ' + noName + ' ' + max(netObject.noType) + ' ' +
max(case when  netObject.noIPAddr  IS NULL then '' else netObject.noIPAddr END ) +
max(case when netObject.noIPAddr_First IS NULL then '' else netObject.noIPAddr_Last END ) + ' ' +
max(case when netObject.noNetmask IS NULL then '' else netObject.noNetmask END ) + ' ' +
max(case when netObject.noIPAddr_Last IS NULL then '' else netObject.noIPAddr_Last END )
from destination join netObject on destination.noPK = netObject.noPK where destination.rPK = @rulePK group by noName
Commented:
See

http://msdn.microsoft.com/en-us/library/aa259213(SQL.80).aspx

To just suppress the the msg, use this code SET command.

Your case statements can be condensed by using the isnull or coalesce function, which are both a special type of the case statement.
SET ANSI_WARNINGS OFF
 
select '           ' + noName + ' ' + max(netObject.noType) + ' ' +
isnull(max(netObject.noIPAddr), '') +
isnull(max(netObject.noIPAddr_First), '') + ' ' +
isnull(max(netObject.noNetmask), '') + ' ' +
isnull(max(netObject.noIPAddr_Last), '')
from destination 
join netObject on destination.noPK = netObject.noPK 
where destination.rPK = @rulePK 
group by noName
 
SET ANSI_WARNINGS ON

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Both solutions worked, the first was generic to my problem, this one went the extra mile and is more generic.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.