Kevin Kevin - 2 years ago 64
SQL Question

tsql getting an error "Msg147, level15" Why do I get this error and how to fix it

I have the following code

FROM dbo.FunderCharityTbl
WHERE @ContributionAmount > ( ( sum(TotalContributions) / 100 ) *10 ))
raiserror ('Sorry contribution is refused limit is breached', 16,1)
return 99

And I am getting the following error

Msg 147, Level 15, State 1, Procedure InsertContribution, Line 33 An
aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list, and the column
being aggregated is an outer reference.

What I am Trying to do is check if the "@contributionAmount" (inputted amount) is greater than 10% of all the previous contributions made by the person with the inputted funderID and if it is send an error message

I am relatively new to SQL and am wondering why you can't write the If Exists statement the way I did write it and what do I need to do to fix this error and have my proc perform in the same manner as I wanted.

Answer Source

You can't use a Aggregate function in WHERE clause, but you can use it in HAVING clause

IF EXISTS(  SELECT  1 --@FunderID 
            FROM    dbo.FunderCharityTbl 
            HAVING  @ContributionAmount > ((sum(TotalContributions)/100)*10)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download