question

sali768 avatar image
sali768 asked

Case Statement

create table status (

status_id number,

status varchar2(10))

/

create table employees (

empnum number,

empname varchar2(20)

);

insert into status

values (1, 'ACTIVE');

insert into status

values (2, 'INACTIVE');

insert into status

values (3, 'TERM');

insert into employees

values (77, 'JACK', 3);

insert into employees

values (77, 'JACK',2);

insert into employees

values (88, 'SALLY',2);

insert into employees

values (88, 'SALLY',3);

insert into employees

values (99, 'AMY',3);

insert into employees

values (77, 'JACK', 1);

insert into employees

values (88, 'SALLY',1);

insert into employees

values (99, 'AMY',1);

I'm updating an existing code which is similar as below;

select e.empnum, e.empname, s.status, s.status_id

from employees e

inner join status s on (s.status_id = case when e.empnum <> 77 then 1 else s.status_id end)

/

But I have to modify it to include 1 and 2. Please see below. The below code is not working. What other strategy can I apply without changing the code.

select e.empnum, e.empname, s.status, s.status_id

from employees e

inner join status s on (s.status_id in case when e.empnum <> 77 then (1,2) else s.status_id end)

Any feedback is greatly appreciated!! Thanks in advance.

oracle-sql-developercase-statement
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

(No server nearby to test on...)

Would it work if you changed the ON to something like:

ON (s.status_id = s.status_id AND e.empnum =77) 
OR (e.empnum<>77 AND s.statusid IN (1,2))
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.