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.
| 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! |
|
|
|
|
|
|
t-sql 2012 Warning: Null value is eliminated by an