June 12, 2017 at 8:24 am
Here is my query
select st.Num1, st.Num2, st.Cd,count(*) as Empcount,Min(AMT) as Amount
from emp st left outer join dbo.student S
on st.Num1 = S.Num
and st.Num2 = S.DoNum
and st.Cd = S.CurrCd
group by st.Num1, st.Num2, st.Cd
and in the result set I am receiving some NULL values in the Amount column, Is because of this i am getting the warning. I know if I use SET ANSI WARNINGS OFF then it wont get this message but I want to make sure is there an issue with the query?
June 12, 2017 at 8:53 am
Min(isnull(AMT,0)) should get rid of the warning.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 12, 2017 at 11:10 am
Phil Parkin - Monday, June 12, 2017 8:53 AMMin(isnull(AMT,0)) should get rid of the warning.
It will clear the warning, but it would also change the value being returned...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
SomeNumber INT NULL
);
INSERT #TestData(SomeNumber)
VALUES(5), (10), (NULL), (20);
--==========================
SELECT
MIN(td.SomeNumber),
MIN(ISNULL(td.SomeNumber, 0))
FROM
#TestData td;
June 12, 2017 at 11:24 am
Jason A. Long - Monday, June 12, 2017 11:10 AMPhil Parkin - Monday, June 12, 2017 8:53 AMMin(isnull(AMT,0)) should get rid of the warning.It will clear the warning, but it would also change the value being returned...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;CREATE TABLE #TestData (
SomeNumber INT NULL
);
INSERT #TestData(SomeNumber)
VALUES(5), (10), (NULL), (20);--==========================
SELECT
MIN(td.SomeNumber),
MIN(ISNULL(td.SomeNumber, 0))
FROM
#TestData td;
Jolly good point. Replace the 0 with a suitably large number to avoid this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply