Warning: Null value is eliminated by an aggregate or other SET operation.

Question

Warning: Null value is eliminated by an aggregate or other SET operation.

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
March 24, 2017 at 7:30 pm

#328899

Here is my code - I am using the count in this query, May I know where is the issue on this condition?
DECLARE cursor1 CURSOR STATIC FOR
SELECT COUNT(IIF(HReg = 'ROA' AND ISNULL(UI, '') <> '', 1, NULL)) as Emp
    FROM dbo.temp
OPEN cursor1
FETCH NEXT FROM cursor1 INTO
    @Emp
INSERT INTO dbo.Main
   (
   Amount
   )
   Select
   CASE WHEN ISNULL(UI, '') = '' OR AReg <> 'ROA' THEN 0
    Else T1.AMT / @Emp
   END as TotalAmount,
  FROM dbo.ExpT T1 JOIN dbo.temp T
                 on T1.Id = T.Id

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

mcfarlandparkway - Friday, March 24, 2017 7:30 PM
Here is my code - I am using the count in this query, May I know where is the issue on this condition?
DECLARE cursor1 CURSOR STATIC FOR
SELECT COUNT(IIF(HReg = 'ROA' AND ISNULL(UI, '') <> '', 1, NULL)) as Emp
    FROM dbo.temp
OPEN cursor1
FETCH NEXT FROM cursor1 INTO
    @Emp
INSERT INTO dbo.Main
   (
   Amount
   )
   Select
   CASE WHEN ISNULL(UI, '') = '' OR AReg <> 'ROA' THEN 0
    Else T1.AMT / @Emp
   END as TotalAmount,
  FROM dbo.ExpT T1 JOIN dbo.temp T
                 on T1.Id = T.Id

There is more than one issue with this code. Using a CURSOR to assign a single value to a variable is one of them (why the devil are you doing that?).
But the NULL value eliminated warning comes because you are counting NULLs (line 2 of your code).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

mcfarlandparkway - Friday, March 24, 2017 7:30 PM
Here is my code - I am using the count in this query, May I know where is the issue on this condition?
DECLARE cursor1 CURSOR STATIC FOR
SELECT COUNT(IIF(HReg = 'ROA' AND ISNULL(UI, '') <> '', 1, NULL)) as Emp
    FROM dbo.temp
OPEN cursor1
FETCH NEXT FROM cursor1 INTO
    @Emp
INSERT INTO dbo.Main
   (
   Amount
   )
   Select
   CASE WHEN ISNULL(UI, '') = '' OR AReg <> 'ROA' THEN 0
    Else T1.AMT / @Emp
   END as TotalAmount,
  FROM dbo.ExpT T1 JOIN dbo.temp T
                 on T1.Id = T.Id

Here:
SELECT COUNT(IIF(HReg = 'ROA' AND ISNULL(UI, '') <> '', 1, NULL)) as Emp
Note that the message is for informational purposes and does not indicate an error.

Your cursor is pointless. Only one row will ever be returned because you are using COUNT without GROUP BY. The statement is easier to understand if it's written in a more conventional manner:
SELECT COUNT(*) as Emp
FROM dbo.temp
WHERE HReg = 'ROA' AND UI <> ''
You don't need the null check. NULL isn't equal to anything, it's not unequal to anything either.

Since it will only return a single row and column, you can load the result directly into the variable:
SELECT @Emp = COUNT(*)
FROM dbo.temp
WHERE HReg = 'ROA' AND UI <> ''

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]