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

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  March 25, 2017 at 10:59 am

  #1934937

  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).

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  March 25, 2017 at 11:05 am

  #1934938

  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]