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 a field measurement, and if it lasts 6 characters, I want to display four 0, otherwise three 0. I can get an error:

Incorrect syntax next to '='.

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 

In addition, you mixed aggregates with non-aggregates.

Update

You should just lose 'NonDesMin' . Explanation: when you enter a "variable" immediately after CASE , you may have your WHEN clauses comparing equality with your variable. So your SQL could also be like this:

 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 

However, you are using CASE in this format:

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

Answer 2

 select MIN(Measurement), (Case WHEN len(measurement) = 6 then '0000' ELSE '000' END) as [Min] from LeachingView where DateTimeStamp > '2011-01-01' and measurement > 0 

Answer 3

  (select min(measurement) from LeachingView where measurement > 0 and inspectionid = RIGHT('000000000' + part_desc, 10) and datetimestamp > '2010-12-31' ) as [Non-Destructive Min], 

It turns out I don't even need a case statement, but this post helped me learn how to use them. instead, I just used a code that added 10 0 in front of the number. then I took 8 characters. this is what this code did

  RIGHT('000000000' + part_desc, 10) 

Thanks everyone for the message.

Answer 4

I assume that you want to add to the beginning of the measurement.

 SELECT CONCAT(Case WHEN LEN(Measurement) = 6 THEN '0000' ELSE '000' END, MIN(Measurement)) AS Measurement FROM LeachingView WHERE DateTimeStamp > '2011-01-01' AND Measurement > 0;