find a value based on a value

[meridian99]

Is there a technique in oracle sql/pl that I can lookup an associated value based on a code? 1 = crew A, 2 = crew B, 3 = crew C
Instead of having to use multi IF statements to determine the crew code based on the numeric value.

thanks!

 

[lewisp]

WHERE DECODE(code_column,
             1,'crew A',
             2,'crew B',
             3,'crew C',
             'unknown crew') = 'crew A'

 

[SantaMufasa]

Meridian,

Were you looking for a solution that works in SQL or must the solution work for PL/SQL? If your need is for PL/SQL, then DECODE won't work. I posted a PL/SQL-compatible solution for you in your identical Oracle 9i-forum posting, thread759-1058878.

BTW, you never need to double post amongst the four core Oracle db forums...we generally look at all four forums whenever we check Tek-Tips.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[carp]

SQL> select * from my_table;
   CREW_ID
----------
         1
         2
         4
SQL> SELECT CASE crew_id
2            WHEN 1 THEN 'crew A'
3            WHEN 2 THEN 'crew B'
4            ELSE ('unknown crew')
5         END AS crew
6  FROM my_table;

CREW
------------
crew A
crew B
unknown crew