Re: CASE construct in SELECT statement

If you want to do this in PL/SQL, you're out of luck. It only works as a  

SQL-statement in e.g. SQL*Plus.
In PL/SQL, you can replace it by something like :

SELECT firstname, lastname, decode(sign(salary 80000), 1, 'big time' ,
decode(sign(salary 20000), -1, 'small fry', 'middle management')) from employee table

It might be more readable when you first create a function:

create or replace function salary desc(p salary IN NUMBER) as   v rc VARCHAR2(30);
begin
  IF p salary > 80000 THEN
    v rc := 'big time';
  ELSIF p salary < 20000 THEN
    v rc := 'small fry';
  ELSE
    v rc := 'middle management';
  END IF;
  RETURN v rc;
END salary desc;
/

SELECT firstname, lastname, salary desc(salary) from employee table

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2001-01-18, 6:46:40, schreef "Anson Parker" <ans@ nospam x64.net> ove r
het thema CASE construct in SELECT statement:

> Ok, I'm pretty green with Oracle - I'm a MySQL user most of the
> time. I'm currently trying to provide Oracle support in a web applicat
 ion
> I've built currently for MySQL. So far I've been able to survive on th
 e
> Oracle Technet docs - that is until now...
 

> (BTW: I'm running Oracle 8.1.6 on Linux)
 

> In MySQL I have a rather large select statement making use of a case
> construct. It looks something like this (to use the infamous employee

e.g.):

> SELECT firstname, lastname, CASE WHEN salary>80000 THEN 'big time'
> WHEN salary < 20000 THEN 'small fry' ELSE 'middle management' END
 

> FROM employee table;
 

> You should be able to see the basic principle anyway. I'd like to do t
 his
> same
> logic in a PL/SQL statement to my Oracle database (obviously the above
 

> example is a custom MySQL extension).
 

> Can I do this inline (within a SELECT statement)? Or do I have to vent
 ure
> into
> the deep unknown of procedures? It's not a common query and it's the o
 nly
> time I have to stray beyond basic SQL calls so I'd rather stick to the
 

> inline
> method, albeit a little offensive to the eye, if I can.
 

> Anson.
Received on Thu Jan 18 2001 - 00:40:37 CST