SQL Syntax

I have the following SQL code that works properly for generating a single value if I search for one record within a database.

I am not being asked to come up with this value for every record in the database so that I can create additional groupings.

I can add a "where" clause and get a single record, but when I take the "where" clause out to get every value, I get the following error:

arithmetic overflow error converting int to data type numeric

How can I change this code to return a single value for the 1500 plus records?
EE-SQL-Script.txt
mounty95Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Increase your decimal:

try:
select 

(
convert (decimal(18,2),
(sum
(
(CASE roadways WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN '55' else 0 END)+
(CASE site_appearance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE exterior WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE playground WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE sidewalks WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE walls WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ceilings WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ac WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE chilled_water_distribution WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE verticalconveyance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE site_utilities WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE gutters WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE windows WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE flashing WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE roof_drains WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE rooftop_equip WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE skylights WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_appearance WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE floors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_doors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE lighting WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE equipment_rooms WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE fcu_radiators WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE steam_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE hot_water_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE exterior_structural WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE entryways WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE roof WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_distribution WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+ 
(CASE electrical_service WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE fire_safety WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE boilers WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE ventilation WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE plumbing WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE int_sub_struct WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)
)
)
)
/
(70-
sum
(
(case roadways when 'Not Applicable' then 1 else 0 end) + (case site_appearance when 'Not Applicable' then 1 else 0 end)
+(case exterior when 'Not Applicable' then 1 else 0 end) + (case playground when 'Not Applicable' then 1 else 0 end)
+(case sidewalks when 'Not Applicable' then 1 else 0 end)+(case walls when 'Not Applicable' then 1 else 0 end)
+(case ceilings when 'Not Applicable' then 1 else 0 end)+(case ac when 'Not Applicable' then 1 else 0 end)
+(case chilled_water_distribution when 'Not Applicable' then 1 else 0 end)+(case verticalconveyance when 'Not Applicable' then 1 else 0 end)
+(case site_utilities when 'Not Applicable' then 2 else 0 end)+(case gutters when 'Not Applicable' then 2 else 0 end)
+(case windows when 'Not Applicable' then 2 else 0 end)+(case flashing when 'Not Applicable' then 2 else 0 end)
+(case roof_drains when 'Not Applicable' then 2 else 0 end)+(case rooftop_equip when 'Not Applicable' then 2 else 0 end)
+(case skylights when 'Not Applicable' then 2 else 0 end)+(case interior_appearance when 'Not Applicable' then 2 else 0 end)
+(case floors when 'Not Applicable' then 2 else 0 end)+(case interior_doors when 'Not Applicable' then 2 else 0 end)
+(case lighting when 'Not Applicable' then 2 else 0 end)+(case equipment_rooms when 'Not Applicable' then 2 else 0 end)
+(case fcu_radiators when 'Not Applicable' then 2 else 0 end)+(case steam_distribution when 'Not Applicable' then 2 else 0 end)
+(case hot_water_distribution when 'Not Applicable' then 2 else 0 end)+(case exterior_structural when 'Not Applicable' then 3 else 0 end)
+(case entryways when 'Not Applicable' then 3 else 0 end)+(case roof when 'Not Applicable' then 3 else 0 end)
+(case electrical_distribution when 'Not Applicable' then 3 else 0 end)+(case electrical_service when 'Not Applicable' then 3 else 0 end)
+(case fire_safety when 'Not Applicable' then 3 else 0 end)+(case boilers when 'Not Applicable' then 3 else 0 end)
+(case ventilation when 'Not Applicable' then 3 else 0 end)+(case plumbing when 'Not Applicable' then 3 else 0 end)
+(case int_sub_struct when 'Not Applicable' then 3 else 0 end)
)
)
+5
) 
as [Overall Rating]

from maintenance_survey_results m

where PSC_Number='01.001' 
ratings

Open in new window

more about the decimal:
http://msdn.microsoft.com/en-us/library/ms187746.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
<Not the answer, but...>
If you look at the first CASE line,
.. THEN 65 WHEN 'Poor' THEN '55' else 0 END)+

Open in new window

all of the values being assigned are numbers, but there are single quote marks around 55, which means it's going to be interpreted as a string.  You'll want to remove them.
.. THEN 65 WHEN 'Poor' THEN 55 else 0 END)+

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
The varchar will be converted:

select 1  a into #temp

select sum(a)
from
(
 select a from #temp
   union
  select '2') b
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.