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.

Make sure to have a look at my answer. The most voted one is on the right track but doesn't promote reuse. — plalx, Feb 19 '14 at 14:24

score 5 · Accepted Answer · edited Feb 19 '14 at 13:52

5

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.

edited Feb 19 '14 at 13:52

Alex K.

171,639
30
264
288

answered Feb 19 '14 at 13:50

Adam Diament

4,290
3
34
55

P.S If it's not obvious already, you will need to populate the ThingNames table with the ids and names of all your different things (just one row for each one), and make sure the thing_id's in your thing table are equal to the ids from ThingName. – Adam Diament Feb 19 '14 at 13:53
ok, due to the where condition `ThingName` will never be `NULL`, but still wouldn't an `INNER JOIN` be more appropriate here? – DrCopyPaste Feb 19 '14 at 13:53
Perhaps it would, but the left join is fine for the original question you asked though given your where clause. Cheers for the bump up. – Adam Diament Feb 19 '14 at 14:00
Looks like what I had in mind, using textual IDs to point at specific entries and leave numeric IDs in the background to make the actual join. Is this in anyway a best practice or are there downfalls to this method? – leokhorn Feb 19 '14 at 18:47
Leokhorn - In general yes, this is standard relational database design best practice: For all but the smallest of databases, it will save you storing masses of duplicate text (this is very bad for performance) in your main data table(s). There are different types of joins for different situations (inner, left, outer, etc) but that is beyond the scope of this question – Adam Diament Feb 20 '14 at 12:38

score 5 · Answer 2 · answered Feb 19 '14 at 13:52

5

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.

answered Feb 19 '14 at 13:52

Gordon Linoff

1,242,037
58
646
786

That seems quite repetitive however if you need this logic in a few different queries and I do not see the advantage over a physical lookup table? – plalx Feb 19 '14 at 13:59
@plalx . . . Is there something about the statement: "I would recommend an approach like this. But you could also do:" that you don't understand? I offered the second approach because it looks more like the constants in the OP's original question. – Gordon Linoff Feb 19 '14 at 14:25
Both of your suggestions have the same drawbacks... I dont get your point? – plalx Feb 19 '14 at 15:11
I hope to have lookup tables in the DB to start with, but if I don't, this would be a nice trick to compensate. Appreciated. – leokhorn Feb 19 '14 at 18:46

plalx · Answer 3 · 2014-02-19T14:06:36.233

A few solutions were proposed already, but if you want to avoid joining the types table all the time to get the descriptions you could create a UDF. I am not sure what kind of negative impact it could have on performances however.

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

You could also make the type code part of a view already, so that you can do:

SELECT thing_id
FROM vThing
WHERE thing_type_code IN ('OPENED', 'ONHOLD')

Or, if it makes sense and apply to your domain, you could as well make the string code the id itself. I've never really done it myself and always favored a unique constrained additionnal code column, but I guess it could be a viable solution.

However, the advantage of having an extra code column for that purpose is that you can give codes only to types you actually need to reference in queries, removing the burden of finding codes for types which do not need one.

Using the code column as a primary key makes me wonder about the performance impact. I've always seen numeric IDs used as primary keys, so I'm guessing there's a reason for it. The view idea is a good one to keep in mind. — leokhorn, Feb 19 '14 at 18:51

score 1 · Answer 4 · answered Feb 19 '14 at 13:50

1

I assume that OPENED and ONHOLD are meant to be strings, rather than columns in your queries.

You can do this; if your database is relational, the thing_type column should have a foreign key into another table, which will have those values. Thus, your query becomes:

select thing_id
  from thing t
  join thing_types tt
    on t.thing_type_id = tt.id
 where tt.description in ('OPENED', 'ONHOLD')

You can find out if there's a foreign key by querying USER_CONSTRAINTS.

select *
  from user_constraints
 where table_name = 'THING'

answered Feb 19 '14 at 13:50

Ben

51,770
36
127
149

OPENED and ONHOLD *should* be strings in a perfect world, but I've seen tables with a "type" column with only numbers, and no string column or lookup table in sight, maybe just a field comment to let you know what these numbers mean. – leokhorn Feb 19 '14 at 18:53
I don't see a problem with a type column and only numbers (as long as there's a foreign key). I was referencing the fact that you didn't enclose them in quotes in your question, which implies that they're columns rather than strings. – Ben Feb 19 '14 at 18:59
I meant there are, of course, no foreign key... just numbers, linked to nothing but an abstract definition in the mind of a developer somewhere. As for my syntax, I used a convention from Java where constants are in all caps. OPENED would be a variable containing a numeric value. – leokhorn Feb 19 '14 at 22:04

score 1 · Answer 5 · answered Feb 19 '14 at 13:55

Another way without join using in, I think this would run faster, but you could test that against your real data.

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
where thing.thing_type_id in (select ThingName.id from ThingName WHERE ThingNames.ThingName IN ('OPENED', 'ONHOLD'))

Table & column names copied from Diamond Fox's answer

Using human-readable constants in queries

5 Answers5

Linked