Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Arithmetic Error Converting Int to Numeric when Datatype is Already Int

I have the following update statement that is failing with the notorious Arithmetic overflow error converting int to data type numeric.  Problem is that the data type for the field is already numeric.  My question is how do I modify the update to return a result?  And how would I modify the code to return one of 5 results if the result of the calculation falls within ranges?  In other words, if the calculation results falls within the range 100-95 then update the field with the word "Superior" if between 95-85 then "Good" etc.

I am omitting all the middle calculation as it is a book, but here is the basic code:

update FACILITY
set MAINTANCE_RATING=(

select
--Add additional fields here such as PSC #, School Name, LEA Name
--Code below calculates the Overall Rating for a Facility based on Methodology developed in FY 2007
(
convert (decimal(6,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)+
....
)
)
)
/
(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)
)
)
+5
)
as [Overall Rating]

from maintenance_survey_results m

)
from Maintenance_Survey_Results,FACILITY
where Maintenance_Survey_Results.PSC_Number=FACILITY.PSC_NUMBER
0
mounty95
Asked:
mounty95
  • 9
  • 6
  • 2
  • +2
1 Solution
 
rajvjaCommented:
Hi

Instead of decimal(6,2), try using just decimal
0
 
rajvjaCommented:
Hi,

   May be the query resulting second part(after /) returning 0.
0
 
Olaf DoschkeSoftware DeveloperCommented:
So simplifying the calculation Sum(...) ends up as some int and you also divide by 70- some other summation, another int, overall the calculation is convert(decimal(6,2),someint/someint).

Well, you can't expect this to have 2 decimal places, therefor the error. I'd rather use CAST() instead of CONVERT(), as that will round the computed value.

CAST(sum(...)/(70-sum()) As decimal(6,2)) as [overall_rating]

Open in new window


Also, what's the exact data type of the field you talk of? Is it really decimal(6,2)?

In regard to converting the overall rating into text ratings, you can again make use of CASE

Select [overall_rating],
Case
           WHEN   100>=[overall_rating] and [overall_rating]>95 Then 'Superior'
           WHEN   95>=[overall_rating] and [overall_rating]>85 Then 'Good'
        ...
Endcase AS [overall_textrating]
From ...

Open in new window


Bye, Olaf.

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
mounty95Author Commented:
Changing from decimal (6,2) to just decimal returned a result, but the same result for all those records effected.  The result in the column says -680.

The result after the "/" can not result in 0 based on the logic.

Is the same result because of the way that I am trying to link the two tables?  If so, how do I resolve?

Also, how would I change the update to enter a word of Superior/Good/Adequate/Not Adequate/Poor depending on the result of the math?
0
 
lcohanDatabase AnalystCommented:
What is your TARGET column datatype?

For the word you can build a case like below:
.......
CASE WHEN table.column = 10 THEN  Superior
            WHEN table.column  < 10 AND table.column >= 8 THEN  Good
            WHEN table.column  < 8 AND table.column >=6 THEN  Adequate
            WHEN table.column  <  6 AND table.column >=4 THEN  Not Adequate
            WHEN table.column  < 4 THEN  Poor  ELSE NotRated END as RatingColumn,
.......

0
 
lcohanDatabase AnalystCommented:
Sorry the quotes around the strings got stripped out misteriously...


CASE WHEN [table].[column] = 10 THEN  'Superior'
            WHEN [table].[column]  < 10 AND [table].[column] >= 8 THEN  'Good'
            WHEN [table].[column]  < 8 AND [table].[column] >=6 THEN  'Adequate'
            WHEN [table].[column]  <  6 AND [table].[column] >=4 THEN  'Not Adequate'
            WHEN [table].[column]  < 4 THEN  Poor  ELSE 'NotRated' END as RatingColumn
0
 
mounty95Author Commented:
The target column datatype is int.

Here is the entire script if that helps.  I now get the same result of -680 in  every column even though there is not a calculation for every record.  I feel like I am going backwards here:

update FACILITY
set MAINTANCE_RATING=
(
select

(
convert (decimal,
(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,FACILITY f
where m.PSC_Number=f.PSC_NUMBER)
0
 
mounty95Author Commented:
Where would I insert the case to get the wording?
0
 
lcohanDatabase AnalystCommented:
So, here is the SELECT statement but if you need to perform an update then obviously you will need the TARGET column for your RATING and should be easy for you to write the update statement.

....
select

(
convert (decimal,
(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],

CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn

from maintenance_survey_results m,FACILITY f
where m.PSC_Number=f.PSC_NUMBER
0
 
mounty95Author Commented:
I get an Invalid column name 'Overall Rating' for each line at the bottom and then
Only one expression can be specified in the select list when the subquery is not introduced with Exists.

Not sure what to do with these.
0
 
SharathData EngineerCommented:
Can you just run this and post the result.
SELECT (CONVERT(DECIMAL,(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 
       JOIN FACILITY f 
         ON m.PSC_Number = f.PSC_NUMBER

Open in new window

Do you want to update MAINTANCE_RATING column n FACILITY with this value for all th records?
0
 
mounty95Author Commented:
The script returns -680 which is what was updated into the Facility table for every record.

I have two tables:  Maintenance_survey_results and Facility.  The two tables are joined by the PSC_number field.  I need to update the MAINTANCE_RATING field in the Facility table for each record in the Maintenace_survey_results table and the join is not equal.  There are not equal (there are more records in the Facility table than in the Maintenace_survey_results table.
0
 
SharathData EngineerCommented:
>> The script returns -680 which is what was updated into the Facility table for every record.

Is that what you are looking for?
0
 
mounty95Author Commented:
No each record in the Maintenance_survey_results should return a value between 0-100.  The records in the Maintenance_survey_results represent an inspection of a facility.  I want to update the maintnance_results field in the Facility table with the calculated result from the Maintenance_survey_results table.  There join is not equal as there are more facilities in the Facility table than there are in the Maintenance_survey_results table.
0
 
mounty95Author Commented:
Overall Rating is an alias for the calculation that is performed.  The errors that I receive on the Case must be because Overall Rating can not be evaluated, so where must I put the case syntax to make evaluate the whole calculation?
0
 
lcohanDatabase AnalystCommented:
That's true - my bad.....hard to work on pseudo code sometimes. Thi happens because of the alias column so a statement like below fails


select 7 as [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn


but think that you can put the big SELECT in a view then use that view to build the case like in example below it will workj 100%:


           
create view test_view as
select 7 as [Overall Rating]

Select [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn
FROM test_view
0
 
lcohanDatabase AnalystCommented:
More speciffic for your case:

CREATE VIEW test_view AS
select
(
convert (decimal,
(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,FACILITY f
where m.PSC_Number=f.PSC_NUMBER
GO


SELECT [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn
FROM test_view
GO
 
0
 
mounty95Author Commented:
My problem is that I am trying to do an update like:

update facility
set maintnance_result=

Do calculation

If calculation is between 100 and 95 then update with Superior
If calculation is between 95 and 85 then update with Good

where recordid from facility table = recordid from maintenance_survey_results
0
 
lcohanDatabase AnalystCommented:
Well you could have all the calculations done by the SQL VIEW and please add following column to your view:

CREATE VIEW test_view AS
select m.PSC_Number,
(
convert (decimal,.......................


Then build your update statement based on the view - something like:

UPDATE FACILITY SET MAINTANCE_RATING = test_view.[Overall Rating],
                              Rating = CASE WHEN [Overall Rating] = 10 THEN  'Superior'
                                                WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
                                                      WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
                                                      WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
                                                WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END
FROM test_view WHERE FACILITY.PSC_Number=test_view.PSC_NUMBER

0
 
mounty95Author Commented:
Not quite what I wanted, but thank you for offering a solution.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 9
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now