t-sql 2012 Warning: Null value is eliminated by an

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql 2012 Warning: Null value is eliminated by an

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-04-28 : 17:40:31

In a t-sql 2012 query that will be updated in a stored procedure I am getting the following warning message:

Warning: Null value is eliminated by an aggregate or other SET operation. I would like to get rid of this warning missing without just turning off the warning messages.

I would like to change the sql so that it does not occur. The following is my new sql that generates the warning:

case when (coalesce(a.status,ae.status) = 'A') and (IsNull(ae.excuse, 'U') = 'U') and (IsNull(ae.code, 'DRC') = 'DRC') then
sum(DATEDIFF(minute,pm.startTime,pm.endTime)-coalesce(pm.lunchTime,0)-coalesce(a.presentMinutes,0)) else 0 end as DRCMinutes,

The sql is part of a select statement. Thus can you show me how to modify the sql that I just listed and explain why your change would make a difference?

Kristen
Test

22859 Posts

Posted - 2015-04-29 : 09:37:01

[code]SUM(COALESCE( ... original code ..., 0)[/code]

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-04-29 : 10:26:16

could you give more of an explanation and more code how to solve the issue?

jleitao
Posting Yak Master

100 Posts

Posted - 2015-04-29 : 11:49:21

probably one (or both) this columns ( pm.startTime or pm.endTime )) have a row (or more) with NULL value.

If one of them are null your DATEDIFF will return null. then the result of the calculation NULL - coalesce(pm.lunchTime,0)-coalesce(a.presentMinutes,0) will be null.

a simple sample, if you execute this query you will have 1 row with null because column a is null:

WITH tbl as (
SELECT 1 as a, 2 as b
union
SELECT NULL as a, 3 as b
union
SELECT 5 as a, 1 as b
)

SELECT
a+b
from tbl

and if you execute the query above you will receive that warning, but the final result its correct:

WITH tbl as (
SELECT 1 as a, 2 as b
union
SELECT NULL as a, 3 as b
union
SELECT 5 as a, 1 as b
)

SELECT
sum(a)
from tbl

that could be a problem if you want to do a count, for example:

WITH tbl as (
SELECT 1 as a, 2 as b
union
SELECT NULL as a, 3 as b
union
SELECT 5 as a, 1 as b
)

SELECT
count(a)
from tbl

however look at the pm.startTime and pm.endTime columns and try resolve your warning.

------------------------
PS - Sorry my bad english

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-04-29 : 12:03:26

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources