[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-15
3
Medium Priority
?
1,245 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
0
Comment
Question by:rowansmith
3 Comments
 
LVL 39

Expert Comment

by:appari
ID: 23646950
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
0
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 23647215
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

0
 
LVL 11

Author Closing Comment

by:rowansmith
ID: 31547184
Both solutions worked, the first was generic to my problem, this one went the extra mile and is more generic.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question