August 24, 2010 at 1:08 pm
This is a snippet of a bigger process.
I basically want to get rid of this message "Warning: Null value is eliminated by an aggregate or other SET operation."
Because I am grouping at the end I am using the MAX() functions through out. They are causing me to get the above warning, I think.
There should be a match on only one of the LEFT JOINS with the AutoBatchClientConfig table.
When ran it will return a series of SQL statements that look similar to this:
EXECUTE sp_ExecuteSQL N'DECLARE @BatchRequestID INT EXEC GFS.dbo.spBatchInsertConsecutive 63557, 50, ''08/24/2010'', 2, ''08/24/2010'', 2, '''', NULL, 1, ''AUTOBATC: Auto Batch'', @BatchRequestID OUTPUT INSERT INTO #Temp (BatchRequestID, MaxBatchSize, OfferID) values(@BatchRequestID,50, 63557)'
The variances for the above statement come from the data in the AutoBatchClientConfig table.
My goal is to not have huge CASE statements for each line applicable.
Any thoughts?
Thank you for your time.
SELECT
@Query = REPLACE (
(
SELECT
X.Command AS [data()]
FROM
(
SELECT
[Command] =
'EXECUTE sp_ExecuteSQL N''DECLARE @BatchRequestID INT EXEC GFS.dbo.spBatchInsertConsecutive ' +
CAST(O.OfferID AS VARCHAR) + ', ' +
CAST(COALESCE(MAX(A1.MaxBatchSize),MAX(A2.MaxBatchSize),MAX(A3.MaxBatchSize)) AS VARCHAR) + ', ' +
'''''' + CONVERT(VARCHAR(10), GETDATE(), 101) + ''''', ' +
CAST(((COUNT(DISTINCT S.SubmitID) / COALESCE(MAX(A1.MaxBatchSize),MAX(A2.MaxBatchSize),MAX(A3.MaxBatchSize))) + 1) AS VARCHAR)+ ', ' +
'''''' + CONVERT(VARCHAR(10), GETDATE(), 101) + ''''', ' +
CAST(COALESCE(MAX(A1.BatchType),MAX(A2.BatchType),MAX(A3.BatchType)) AS VARCHAR) + ', ' +
'''''''''' + ', ' +
'NULL' + ', ' +
'1' + ', ' +
'''''' + COALESCE(MAX(A1.BatchKey),MAX(A2.BatchKey),MAX(A3.BatchKey)) + ': Auto Batch''''' + ', ' +
'@BatchRequestID OUTPUT ' +
'INSERT INTO #Temp (BatchRequestID, MaxBatchSize, OfferID) values(@BatchRequestID,' + CAST(COALESCE(MAX(A1.MaxBatchSize),MAX(A2.MaxBatchSize),MAX(A3.MaxBatchSize)) AS VARCHAR) + ', ' + CAST(O.OfferID AS VARCHAR) + ')'' |'
FROM
GFSOffer.dbo.Submission S (NOLOCK)
JOIN
GFS.dbo.Offer O (NOLOCK) ON S.OfferID = O.OfferID
JOIN
GFS.dbo.OfferClient OC (NOLOCK) ON S.OfferID = OC.OfferID
LEFT JOIN
GFStemp.dbo.AutoBatchClientConfig A1 (NOLOCK) ON OC.ClientID = A1.ClientID AND
OC.OfferID = A1.OfferID
--client specific
LEFT JOIN
GFStemp.dbo.AutoBatchClientConfig A2 (NOLOCK) ON OC.ClientID = A2.ClientID AND
A2.OfferID = 0
--default for everything else
LEFT JOIN
GFStemp.dbo.AutoBatchClientConfig A3 (NOLOCK) ON A3.ClientID = 0 AND
A3.OfferID = 0
WHERE
S.BatchID = 0 AND
O.HoldBatch = 0 AND
(
O.CloseDate >= GETDATE() OR
O.CloseDate IS NULL
) AND
NOT EXISTS (
SELECT
1
FROM
GFStemp.dbo.AutoBatchClientsToIgnore T (NOLOCK)
WHERE
T.ClientID = OC.ClientID
) AND
EXISTS (
SELECT
1
FROM
GFS.dbo.OfferProcess OP (NOLOCK)
WHERE
OP.OfferID = O.OfferID
)
GROUP BY
O.OfferID
) X
FOR XML PATH ('')
),'|', CHAR(13)+ CHAR(10)
)
August 25, 2010 at 12:15 am
USe
set ansi_warnings off
above your code
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 25, 2010 at 9:39 am
Thanks, I wasn't even thinking of a non-code changing solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply