minichicken
asked on
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.LastExpen seReg, GetDate()) >= 28) AND (DateDiff(Day, R1.LastPOReg, GetDate()) >= 28))
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
ISNULL(MAX(ESL.DateOfEntry
ISNULL(MAX(RH.CreatedDate)
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.LastExpen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Get rid of NULL values on your database. Simplest and long lasting effect solution.
ASKER
Perfect Mr_Peerapol, you the best!!
SET ANSI_WARNINGS OFF
or
https://www.experts-exchange.com/questions/21338132/Warning-Null-value-is-eliminated-by-an-aggregate-or-other-SET-operation.html
read also:
http://www.sqlservercentral.com/columnists/mcoles/gotchasqlaggregatefunctionsandnull.asp