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?
 
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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

0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.