Sunday, February 19, 2012

Error message for null at SET or aggregate

The following code works perfectly when run by itself:

DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime

SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)

However, it is part of a long stored procedure, and when it runs there, I get the following instead of the line confirming the two dates:

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

What could be interfering with this section of the stored procedure?

Further research has detected that the following code:

DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)

although the following part isolated returns the correct value:

DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)

Why is the variable not being set?


|||

WHEN THE FOLLOWING CODE IS RUN IN ISOLATION:

DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime

SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)

IT RESULTS IN THIS:

The Transaction Date is May 15 2006 12:00AM. The Trade Date is May 15 2006 12:00AM

WHEN THE CODE IS RUN AS PART OF A LONGER STORED PROCEDURE, IT RESULTS IN THIS:


The Transaction Date is null. The Trade Date is May 15 2006 12:00AM

WHAT IS HAPPENING?

|||

I added an update earlier in the stored procedure and it solved the problem.

Thanks for your help.

No comments:

Post a Comment