?
Solved

SQL SP Error -

Posted on 2014-12-17
1
Medium Priority
?
59 Views
Last Modified: 2014-12-19
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)

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

Open in new window

0
Comment
Question by:Scott Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 60

Accepted Solution

by:
HainKurt earned 1500 total points
ID: 40505504
try

insert into #prtemp select 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 charindex(',' + rt.ReserveTypeID + ',', ',' + @ReserveType+ ',') >0 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

Open in new window

0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question