Is there a way to have a SQL statement return a string value from a map that has an integer as the key?

Question

For example:

In the database we have number representations of fields that correspond to strings in the app. We store the data as an integer for size and speed reasons. During testing we compare what's in the app with SQL statement results. The problem is that we see the numbers in the results but there are strings in the app. There is an enum in the code that defines what those numbers mean in terms of a string. Instead of having to look at the enum everytime I want to compare ... is there a way to make the SQL results show the string in a map that I put in the SQL select statement instead of the integer value that's actually in the database?

Answer 1

You can recreate your Enum in SQL using a CASE statement like this:

select map,
    case map
        when 123 then 'xyz'
        when 456 then 'abc'
    end as StringMap
from MyTable

Even better is to store the Enum as a lookup table. Then you can just join against it:

select m.map, e.StringMap
from MyTable m
inner join MyLookupTable e on m.map = e.map