Scott Williams
asked on
SQL SP Error -
I am modifying an existing stored procedure someone else created. I get an error when I run the query to save my sp.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
The query that is causing the error is attached.
The issue is with this line:
total_incurred = SUM(CASE WHEN rt.ReserveTypeID in(SELECT Item FROM dbo.Split (@ReserveType, ',')) THEN r.ReserveAmount ELSE 0 END)
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
The query that is causing the error is attached.
The issue is with this line:
total_incurred = SUM(CASE WHEN rt.ReserveTypeID in(SELECT Item FROM dbo.Split (@ReserveType, ',')) THEN r.ReserveAmount ELSE 0 END)
insert into #prtemp select
r.claimantId,
FY_process = ltrim(str((@lossfy -1)) + '/' + ltrim(str(@lossfy))),
total_paid = CAST(NULL AS DECIMAL(12, 2)) ,
total_incurred = SUM(CASE WHEN rt.ReserveTypeID in(SELECT Item FROM dbo.Split (@ReserveType, ',')) THEN r.ReserveAmount ELSE 0 END)
FROM dbo.Reserve r ( NOLOCK )
LEFT JOIN dbo.ReserveType rt ON r.ReserveTypeID = rt.ReserveTypeID
where r.ClaimantID = @clmnt AND r.ProcessedDate <= ('6/30/'+ltrim(str(@lossfy)))
GROUP BY r.ClaimantID
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
