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 numeric 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

 

[SantaMufasa]

Meridian,

Here is some sample code to do what you want if you have numeric key values:

set serveroutput on format wrap
declare
    type char_stencil is table of varchar2(10) index by binary_integer;
    val char_stencil;
begin
    val(1) := 'crew A';
    val(2) := 'crew B';
    val(3) := 'crew C';
    for r in (select id, last_name from s_emp) loop
        begin
            dbms_output.put_line(lpad(r.id,3)||' '||
                rpad(r.last_name,15)||val(r.id));
        exception
            when no_data_found then
                dbms_output.put_line(lpad(r.id,3)||' '||
                rpad(r.last_name,15)||'no crew');
        end;
    end loop;
end;
/

  1 Velasquez      crew A
  2 Ngao           crew B
  3 Nagayama       crew C
  4 Quick-To-See   no crew
  5 Ropeburn       no crew
  6 Urguhart       no crew
  7 Menchu         no crew

Let us know if this is the type of thing you were looking for.


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]

Or use the CASE statement. It could be done as a stored function (if you need to use this often) or in a SQL statement (as seen on your identical post in a different forum) or an anonymous PL/SQL script:

CREATE OR REPLACE FUNCTION what_crew(p_id IN NUMBER) RETURN VARCHAR2 IS
BEGIN
   RETURN CASE p_id
             WHEN 1 THEN 'Crew A'
             WHEN 2 THEN 'Crew B'
             ELSE 'Unknown Crew'
          END;
END;
/
select * from my_table;
   CREW_ID
----------
         1
         2
         4
SQL> select what_crew(crew_id) from my_table;

WHAT_CREW(CREW_ID)
--------------------
Crew A
Crew B
Unknown Crew