4
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 '='.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
zach
  • 1,281
  • 7
  • 27
  • 41
  • Because the answer will be "no" if we don't ask: have you double-checked that your 'measurement' column is a CHAR (or varchar, or whatever) type? – AllenG May 17 '11 at 16:14
  • len(measurement) and measurement > 0 used together. measurement seems to be string but is it guaranteed to be an integer as well? – amit_g May 17 '11 at 16:15
  • @Adrian - SQL Server (from the "incorrect syntax near '='" message) – Martin Smith May 17 '11 at 16:19
  • I was assuming that some other RDBMS could provide a similar message. Will retag as soon as available. Also, I fixed my answer since I was mixing aggregates with non-aggregates. – Adriano Carneiro May 17 '11 at 16:22
  • Are you using this to format a numeric value into a fixed number of digits? If so, there are better ways to achieve this. – Damien_The_Unbeliever Jun 01 '11 at 08:04

4 Answers4

4

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
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • I ended up finding out the answer, i posted it below. Thanks for the quick tutorial on case statements. i didn't end up using it but now i know – zach Jun 07 '11 at 16:56
0
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
maple_shaft
  • 10,435
  • 6
  • 46
  • 74
0
      (select min(measurement)
         from LeachingView 
         where measurement > 0 and inspectionid = RIGHT('000000000' + part_desc, 10)                     
             and datetimestamp > '2010-12-31'
  ) as [Non-Destructive Min],

Turns out i didnt even need a case statement, but this post helped me learn how to use them. instead i just used code that added 10 0's the front of the number. then i took the right 8 characters. that is what this code did

  RIGHT('000000000' + part_desc, 10)

Thanks all for the post

zach
  • 1,281
  • 7
  • 27
  • 41
-1

I'm guessing you're wanting to append to to the beginning of 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;
dtbarne
  • 8,110
  • 5
  • 43
  • 49