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 ?

score 1 · Accepted Answer · answered Jan 18 '19 at 00:27

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.

answered Jan 18 '19 at 00:27

Gordon Linoff

1,242,037
58
646
786

Yes! It was the double quotes. I changed it to single quotes and it works great. This isn’t my usual flavor of database, so I am not used to using double quotes at all – Stephen R Jan 18 '19 at 00:56
Worth mentioning: assigning the calculated field name to the same as the origin name was not a problem. – Stephen R Jan 18 '19 at 17:27

score 0 · Answer 2 · answered Jan 18 '19 at 00:35

Example from the official documentation, It does not seem that this would cause problem

SELECT last_name, first_name,
CASE
WHEN scholarship = 1 THEN 'Scholastic'
WHEN citizenship <> 'United States' THEN 'Foreign Study'
WHEN (date_of_birth >= '1960-01-01' AND date_of_birth <= '1970-01-01') THEN 'AJ-44 Funds'
ELSE 'NONE'
END
AS 'Funding Program' FROM Person ORDER BY last_name

Which results as that the problem is not in CASE statement itself, but on the As Position part where the Position is your column name wıth TINYINT data type. You should consider to use alias which is different from your column names.

SQL Case() casts results as integer

2 Answers2