Hi any comments on why the ELSE in the case statement will not work
This code is being used within a SProc and is failing on the ELSE
NOTE: the field attributes are
CoatingsEdge nvarchar (NULLS allowed)
score nvarchar
weight nvarchar
rating nvarchar

also 1st case condition the calculation is rounding up in the nvarchar.

Unfortunately, I didn't create the field attributes and need to work with them for reporting purpose.

--coatings edge

update #temptable
set SII = (select distinct
   Case
   When CoatingsEdge is not null then ((score *1.0)* (weight*1.0) )
   else (weight*1.0)
   END
   from tksii_maincategory
   where maincategory = 'coatings'
   and deficiency = 'edge'
   and rating = CoatingsEdge)

  +
--coatings weld
  (select distinct
   Case
  When CoatingsWeld is not null then ((score *1.0)* (weight*1.0) )
  else (weight*1.0)
  END
  from tksii_maincategory
  where maincategory = 'coatings'
  and deficiency = 'weld'
  and rating = CoatingsWeld)

Hey Cole,

I think your lack of result may be caused by nulls. Your else statement depends on CoatingsEdge being null, but the where clause requires rating to be equal to CoatingsEdge. And that's the spot: when you ask for something to be tested as equal to a null.

Try this to see the dilemma directly and I'll bet you won't get any of the rows where CostingsEdge and rating are really the same but both null:
Select * from tksii_maincategory where rating = CoatingsEdge

To solve this, maybe a union would work. First get the result set where CoatingsEdge is not null, then union it with the set where it is null. This way you can skip the Case statement, and tailor the where clause as needed by each result set.

Stoney

Thanks Stoney,

I did realize the problem just after I posted. I appreciate your response and thanks for verifying my theory.

brgrds