filippo filippo -4 years ago 126
SQL Question

Lookup table for oracle decodes?

It might be a newbie question, but still..

We are all familiar with Oracle's decodes and cases, e.g.

decode (state,
0, 'initial',
1, 'current',
2, 'finnal',
from states_table

Or the same sort of thing using CASE's.

Now let's say I have a table with these same values:

state_num | state_desc
0 | 'initial'
1 | 'current'
2 | 'finnal'

is there a way I could do that same query using this table as a resource for the decode?
Please note that I do not want to joint the table to access the data from the other table... i just want to know if there's something I could use to do a sort of
decode(myField, usingThisLookupTable, thisValueForDefault)

Answer Source

Instead of a join, you could use a subquery, i.e.

select nvl(
   (select state_desc 
   from lookup 
   where state_num=state),to_char(state)) 
from states_table;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download