경고 : Aqua Data Studio의 집계 또는 기타 SET 작업에 의해 Null 값이 제거되었습니다.

경고 : Aqua Data Studio의 집계 또는 기타 SET 작업에 의해 Null 값이 제거되었습니다.

---

데이터가 null이고 결과가 표시 될 때 경고가 표시되면 문제가 있습니다. 이 문제를 해결하는 방법?. 테이블에 데이터가 없을 때 null 데이터를 0으로 변경하는 방법은 무엇입니까?

이것은 내 코드입니다.

SELECT DISTINCT c.username             AS assigner_officer,
                d.description          AS ticketcategory,
                (SELECT Count(closed)
                 FROM   ticket
                 WHERE  assigned_to = c.user_id
                        AND closed IS NOT NULL
                 GROUP  BY assigned_to)closedcases,
                (SELECT Count(closed)
                 FROM   ticket
                 WHERE  assigned_to = c.user_id
                        AND closed IS NULL
                 GROUP  BY assigned_to)opencases
FROM   ticket a
       JOIN ticketlog b
         ON a.ticketid = b.ticketid
       JOIN access c
         ON a.assigned_to = c.user_id
       JOIN ticket_category d
         ON a.cat_code = d.id
       JOIN lookup_department e
         ON a.department_code = e.code 

결과는 다음과 같이 나타납니다.

 Warnings: ---> 
   W (1): Warning: Null value is eliminated by an aggregate or other SET operation.
          <--- 
 assigner_officer     ticketcategory     closedcases     opencases    
 -------------------  -----------------  --------------  ------------ 
 abdulhafiz           Enquiry            (null)          0            
 affan                Enquiry            12              (null)       
 amirul               Enquiry            1               (null)       
 azrul_fahmi          Enquiry            45              0            
 Azwani               Enquiry            (null)          0            
 chai                 Enquiry            4               (null)       
 dalinawati           Enquiry            1               0            
 Emmy                 Complaints         (null)          0            
 Fadhlia              Enquiry            38              0            
 fairulhalif          Others             1               (null)       
 farikh               Enquiry            (null)          0            
 ismailh              Enquiry            28              0            
 izzahanna            Enquiry            (null)          0            
 Kamsuzilawati        Enquiry            1               (null)     

---

주로 COUNTUID를 요약하는 데 사용 합니다. 따라서

COUNT([uid]) 경고를 생성합니다.

경고 : 집계 또는 기타 SET 작업에 의해 Null 값이 제거되었습니다.

왼쪽 조인과 함께 사용되는 동안 계산 된 개체가 존재하지 않습니다.

COUNT(*)이 경우에 사용하면 존재하는 총 결과 (예 : 상위) 수를 계산하므로 잘못된 결과도 렌더링됩니다.

Using COUNT([uid]) IS a valid way of counting, and the warning is nothing more than a warning. However if you are concerned, and you want to get a true count of uids in this case then you could use:

SUM(CASE WHEN [uid] IS NULL THEN 0 ELSE 1 END) AS [new_count]

This would not add a lot of overheads to your query. (tested mssql 2008)

---

One way to solve this problem is by turning the warnings off.

SET ANSI_WARNINGS OFF;
GO

---

Use ISNULL(field, 0) It can also be used with aggregates:

ISNULL(count(field), 0)

However, you might consider changing count(field) to count(*)

Edit:

try:

closedcases = ISNULL(
   (select count(closed) from ticket       
    where assigned_to = c.user_id and closed is not null       
    group by assigned_to), 0), 

opencases = ISNULL(
    (select count(closed) from ticket 
     where assigned_to = c.user_id and closed is null 
     group by assigned_to), 0),

---

You want to put the ISNULL inside of the COUNT function, not outside:

Not GOOD: ISNULL(COUNT(field), 0)

GOOD: COUNT(ISNULL(field, 0))

---

I was getting this error; I just put a WHERE clause for the field which was used within count clause. it solved the issue. Note: if null value exist, check whether its critical for the report, as its excluded in the count.

Old query:

select city, Count(Emp_ID) as Emp_Count 
from Emp_DB
group by city

New query:

select city, Count(Emp_ID) as Emp_Count 
from Emp_DB
where Emp_ID is not null
group by city

---

If any Null value exists inside aggregate function you will face this issue. Instead of below code

 SELECT Count(closed)
  FROM   ticket
  WHERE  assigned_to = c.user_id
  AND closed IS NULL

use like

SELECT Count(ISNULL(closed, 0))
  FROM   ticket
  WHERE  assigned_to = c.user_id
  AND closed IS NULL

참고URL : https://stackoverflow.com/questions/11384292/warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation-in-aqua