Using human-readable constants in queries

Question

Here's an example query:

SELECT thing_id
FROM thing
WHERE thing_type IN (3, 7)

I would like to turn the 3 and 7 into human-readable names to help understand what the query is truly doing. Something like the following would be great:

SELECT thing_id
FROM thing
WHERE thing_type_id IN (OPENED, ONHOLD)

Knowing that OPENED and ONHOLD would have their actual values declared somewhere else.

I'm thinking there may also be a way to do this with a JOIN of a thing_type table.

Note that I'm stuck in an environment where I'm coding queries directly rather than using an abstraction framework.

Answer 1

Assuming you have a linked table called ThingNames where there are two columns, id and ThingName, you could do this

SELECT thing_id

FROM thing

LEFT JOIN ThingNames on thing.thing_type_id = ThingName.id

WHERE ThingNames.ThingName IN ('OPENED', 'ONHOLD')

(Don't forget the quotes around the ThingNames in your in brackets.

Answer 2

You can do this by generating a lookup table for the values:

with Lookup(value, name) as (
      select 3, 'OPENED' from dual union all
      select 7, 'ONHOLD' from dual
     )
SELECT thing_id
FROM thing t
WHERE thing_type_id IN (select value from Lookup where name in ('OPENED', 'ONHOLD'));

I would recommend an approach like this. But you could also do:

with thevalues as (
      select 3 as OPENED, 7 as ONHOLD from dual
     )
SELECT thing_id
FROM thing cross join
     thevalues
WHERE thing_type_id IN (OPENED, ONHOLD);

This is most similar to your original query.