Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1661
  • Last Modified:

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

Hi all

I have this query below when i run it gives a message:
Warning: Null value is eliminated by an aggregate or other SET operation.

How do I solve that warning?

Please kindly assist, thanks

SELECT
R1.JobNumber,
R1.JobName,
R1.ProjectManagerNumber,
CASE WHEN R1.LastTimeReg = '1900.01.01' THEN 'N/A' ELSE R1.LastTimeReg END AS LastTimeReg,
CASE WHEN R1.LastExpenseReg = '1900.01.01' THEN 'N/A' ELSE R1.LastExpenseReg END AS LastExpenseReg,
CASE WHEN R1.LastPOReg = '1900.01.01' THEN 'N/A' ELSE R1.LastPOReg END  AS LastPOReg

FROM
(SELECT
JH.JobNumber,
JH.JobName,
JH.ProjectManagerNumber,
ISNULL(MAX(TSL.PeriodStart), '1900.01.01') AS LastTimeReg,
ISNULL(MAX(ESL.DateOfEntry), '1900.01.01') AS LastExpenseReg,
ISNULL(MAX(RH.CreatedDate), '1900.01.01') AS LastPOReg

FROM
JobHeader JH LEFT OUTER JOIN TimeSheetLine TSL ON JH.JobNumber = TSL.JobNumber
LEFT OUTER JOIN ExpenseSheetLine ESL ON JH.JobNumber = ESL.JobNumber
LEFT OUTER JOIN RequisitionHeader RH ON JH.JobNumber = RH.JobNumber
WHERE
(JH.IsBlocked = 0 AND JH.BlockedForInvoicing = 0 AND JH.Closed = 0 AND JH.Template = 0)
GROUP BY
JH.JobNumber,
JH.JobName,
JH.ProjectManagerNumber
) AS R1

WHERE
R1.ProjectManagerNumber  = '300020'
AND (R1.LastTimeReg != '1900.01.01' OR R1.LastExpenseReg != '1900.01.01' OR R1.LastPOReg != '1900.01.01')
AND ((DateDiff(Day, R1.LastTimeReg, GetDate()) >= 28) AND (DateDiff(Day,R1.LastExpenseReg, GetDate()) >= 28) AND (DateDiff(Day, R1.LastPOReg, GetDate()) >= 28))
0
minichicken
Asked:
minichicken
1 Solution
 
Mr_PeerapolCommented:
I guess that some records might have NULL values in TSL.PeriodStart, ESL.DateOfEntry, RH.CreatedDate.

You can do like this:

ISNULL(MAX(ISNULL(TSL.PeriodStart, '1900.01.01')), '1900.01.01') AS LastTimeReg,
ISNULL(MAX(ISNULL(ESL.DateOfEntry, '1900.01.01')), '1900.01.01') AS LastExpenseReg,
ISNULL(MAX(ISNULL(RH.CreatedDate, '1900.01.01')), '1900.01.01') AS LastPOReg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Get rid of NULL values on your database.  Simplest and long lasting effect solution.
0
 
minichickenAuthor Commented:
Perfect Mr_Peerapol, you the best!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now