CODEWITHSUNDEEP
sqloracle-database
leokhorn
leokhorn

Reputation: 507

Using human-readable constants in queries

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.

Upvotes: 2

Views: 190

Answers (5)

plalx
plalx

Reputation: 43718

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.

Upvotes: 2

tgkprog
tgkprog

Reputation: 4598

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 5

Adam Diament
Adam Diament

Reputation: 4850

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.

Upvotes: 5

Ben
Ben

Reputation: 52883

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'

Upvotes: 1

Related Questions