SQL Case() casts results as integer

Question

Working with a Pervasive SQL database, I have a complex SQL SELECT statement that contains the following:

    CASE l."Position"
        WHEN 3 THEN "PIC"
        WHEN 4 THEN "SIC"
        WHEN 22 THEN "FA"
        ELSE ''
    END AS "Position"

l."Position" is stored in the database as TINYINT. Obviously I'm trying to return a string -- basically convert a numeric code to an understandable label. But the result in that column is always 0 because (apparently) it's outputting an integer field and "PIC" (for example) is 0 when cast as a number. If I change it to...

    CASE l."Position"
        WHEN 3 THEN 123
        WHEN 4 THEN 456
        WHEN 22 THEN 789
        ELSE ''
    END AS "Position"

...it returns the three digit numbers as expected, so I know the Case itself is working fine.

How do I tell Case() to return a string/varchar ?

Answer 1

Is this fixed if you use single quotes for the string constants throughout?

(CASE l."Position"
     WHEN 3 THEN 'PIC'
     WHEN 4 THEN 'SIC'
     WHEN 22 THEN 'FA'
     ELSE ''
 END) AS "Position"

Another possibility is that you should name the new column a different name, like Position_Name. If you are selecting "Position" itself in the query, then the two might be getting confused.