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.
