Solved

SQL SP Error -

Posted on 2014-12-17
1
51 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
1 Comment
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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