CODEWITHSUNDEEP
sqlsql-serversql-server-2005
TheMethod
TheMethod

Reputation: 3000

SELECT CASE WHEN TINYINT field condition met return string

I am using SQL Server 2005. I am trying to do a select from a TINYINT field. If the field's value is 0 I want to select an empty string instead ' ' . The fields name is level. HEre is what I am doing:

SELECT 
[Level] = 
Case 
t.[Level]
    WHEN 0 THEN ' '
    ELSE t.[Level]
END
FROM table t

This code always returns 0. I was trying to troubleshoot the issue and tried this: 

SELECT 
[Level] = 
Case 
t.[Level]
    WHEN 0 THEN 'test'
    ELSE t.[Level]
END
FROM table t

And I got a the error Conversion failed when converting the varchar value 'test' to data type tinyint

So I'm seeing that there is a conversion problem here. I've tried:

SELECT 
[Level] = 
Case 
t.[Level]
    WHEN 0 THEN CONVERT(VARCHAR,t.[level])
    ELSE t.[Level]
END
FROM table t

But this of course still returns 0, just the character, so it's still not doing what I need. I am thinking that there is most likely a better way to do this but am not sure how to approach it. Could anyone give me some advice on how to handle this? Thanks much!

Upvotes: 1

Views: 2287

Answers (2)

Gerardo Lima
Gerardo Lima

Reputation: 6712

For CASE statements, all returned values must be of the same type (or automatically convertable); that's the reason why '' was working, but 'test' wasn´t.

SELECT [Level] = 
Case t.[Level]
    WHEN 0 THEN ' '
    ELSE CONVERT(VARCHAR(3), t.[Level])
END
FROM table t

Upvotes: 3

Jamiec
Jamiec

Reputation: 136174

Your attempt to fix this went the wrong way; you needed to convert the else portion to a varchar to match the varchar empty string:

SELECT 
[Level] = 
Case 
t.[Level]
    WHEN 0 THEN ''
    ELSE CAST(t.[Level] AS VARCHAR(10))
END
FROM table t

Upvotes: 1

Related Questions