SQL Case statement

Question

SELECT MIN(Measurement),     
       (CASE 'NonDesMin'
          WHEN len(measurement) = 6 then '0000'
          ELSE '000'
        END) as [Min]    
  FROM LeachingView 
 WHERE DateTimeStamp > '2011-01-01' 
   AND measurement > 0 

This is my SQL statement. I want to check the length of the field measurement, and if it is 6 characters long i want to display four 0's, else three 0's. i can getting an error:

incorrect syntax near '='.

Answer 1

How about this:

select MIN(Measurement), 

    (Case 
        WHEN len(min(measurement)) = 6 then '0000'
        ELSE '000'
        END) as [Min]

from LeachingView 
where DateTimeStamp > '2011-01-01' and measurement > 0 

Also, you were mixing aggregates with non-aggregates.

Update

You should just lose the 'NonDesMin'. Explaining: when you enter a "variable" right after the CASE, you can have your WHEN clauses compare equality with your variable. So, your SQL could also be like that:

select MIN(Measurement), 

    (Case len(min(measurement))
        WHEN  6 then '0000'
        ELSE '000'
        END) as [Min]

from LeachingView 
where DateTimeStamp > '2011-01-01' and measurement > 0 

That said, you use CASE in this format:

CASE SomeField
   WHEN 1 then 'One'
   WHEN 2 the 'Two'
   else 'Three or more'
end