conditions in Case statement

Question

select min(measurement), part_desc
    (case len(measurement)
        when 6 then '0000' + part_desc
        else '000' + part_desc
        end)
from LeachingView
where DateTimeStamp > '2011-01-01' and measurement > 0
group by measurement, part_desc

That is suppposed to append 0000's to the front of another char, but it doesn't work, and i was wondering why and how to make it correct.

The error I get is this:

'part_desc' is not a recognized built-in function name.

score 4 · Accepted Answer · answered May 17 '11 at 19:11

4

Don't really need a CASE at all...

select
    min(measurement),
    RIGHT('0000000000' + part_desc, 10) AS part_desc
from...

answered May 17 '11 at 19:11

gbn

422,506
82
585
676

score 1 · Answer 2 · answered May 17 '11 at 19:08

1

select min(measurement), 
    case len(measurement)
        when 6 then '0000' + part_desc
        else '000' + part_desc
    end as part_desc
from LeachingView
where DateTimeStamp > '2011-01-01' 
    and measurement > 0                        
group by case len(measurement)
        when 6 then '0000' + part_desc
        else '000' + part_desc
    end

answered May 17 '11 at 19:08

D'Arcy Rittich

167,292
40
290
283

why not tell him why it was broken? that seems like the important part. – nathan gonzalez May 17 '11 at 19:11
that seems to have doubled the amount of 0's preappended – zach May 17 '11 at 19:12

score 1 · Answer 3 · answered May 17 '11 at 19:10

1

I think you want

Select min(measurment),
       (case len(measurement)
         when 6 then '0000' + part_desc
         else '000' + part_desc
        end) AS "part_desc"
From LeachingView
Where...

answered May 17 '11 at 19:10

AllenG

8,112
29
40

conditions in Case statement

3 Answers3