I have a requirement like
when type in (1,2,3) then display only type = 1
only when type = 1 then display only type = 1
only when type = 2 then display only type = 2
only when type = 3 then display only type = 3
what i want is as follows
Name Text Type ID
------------------------------------
John Manager 1 10
Glenn Marketing 3 20
Joseph Accountant 2 30
I tried following query its not working
select * from test_table
where id in(10,20,30)
and (case when type in (1,2,3) then 1
when type = 1 then 1
when type = 2 then 1
when type = 3 then 1
end = 1)
John Manager 1 10
George Accountant 2 10
Pat Marketing 3 10
Glenn Marketing 3 20
Joseph Accountant 2 30
create table test_table
(name varchar2(30),
Text varchar2(30),
type number,
id number);
INSERT INTO "TEST_TABLE" (NAME, TEXT, TYPE, ID) VALUES ('John', 'Manager', '1', '10')
INSERT INTO "TEST_TABLE" (NAME, TEXT, TYPE, ID) VALUES ('George', 'Accountant', '2', '10')
INSERT INTO "TEST_TABLE" (NAME, TEXT, TYPE, ID) VALUES ('Pat', 'Marketing', '3', '10')
INSERT INTO "TEST_TABLE" (NAME, TEXT, TYPE, ID) VALUES ('Glenn', 'Marketing', '3', '20')
INSERT INTO "TEST_TABLE" (NAME, TEXT, TYPE, ID) VALUES ('Joseph', 'Accountant', '2', '30')
Edited by: user1849 on Oct 4, 2010 4:05 PM
Edited by: user1849 on Oct 4, 2010 4:05 PM