Solved

SQL SP Error -

Posted on 2014-12-17
1
50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'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 …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now