February 7, 2012 at 2:36 am
When I try to execute the below query, I get the warning
Warning: Null value is eliminated by an aggregate or other SET operation.
any idea how to eliminate this?
SELECT
T1.Columnname1
,ISNULL(T3.Columnname3,10) as Columnname3
,COUNT(T2.Columnname2) as Columnname2, ISNULL(T3.Columnname3,10) - COUNT(T2.Columnname2)
FROM Table1 T1 WITH (NOLOCK)
LEFT JOIN Table2 T2 WITH (NOLOCK) ON T1.Columnname1 = T2.Columnname5
LEFT JOIN Table3 T3 WITH (NOLOCK) ON T1.Columnname1 = T3.Columnname6 AND T3.Columnname4 = 85
WHERE T1.Columnname1<> -1
GROUP BY T1.Columnname1,T3.Columnname3,T2.Columnname5
HAVING ISNULL(T3.Columnname3,10) > COUNT(T2.Columnname2)
ORDER BY (CAST (COUNT(T2.Columnname2) as DECIMAL)/ISNULL(T3.Columnname3,10))
February 7, 2012 at 2:42 am
The field that you are COUNT()'ing, is there a possibility it contains NULLs?
February 7, 2012 at 2:44 am
This was removed by the editor as SPAM
February 7, 2012 at 2:48 am
Yes bob. Since I'm getting the count of a column tat is left joined by a table 1, there are records listed that are not in T2.
But I can't filter not null records in where clause either since it'll become a inner join.
Need to modify the query accordingly. Can u help me out on this?
February 7, 2012 at 2:52 am
The problem is there are no null values in T2.Columnname2 as such. But since it is left joined with T1, there are some null records as a result of this. So if I use count(isnull(T2.Columnname2,0)), the count will not be correct.
February 7, 2012 at 2:58 am
This was removed by the editor as SPAM
February 7, 2012 at 3:02 am
This was removed by the editor as SPAM
February 7, 2012 at 5:18 am
Thanks Stewart.. It worked as good... 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply