Home » SQL & PL/SQL » SQL & PL/SQL » trying to get two values from case stmt (2 Merged) (oracle 11g)
trying to get two values from case stmt (2 Merged) [message #589772] Wed, 10 July 2013 11:41 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi,
In the following query WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN only 'North office' is being retrieved but how to get 'South office' also along with it.Is there a way to write multiple conditions in THEN clause of case stmt something like THEN 'North office' AND 'South office'.Can anyone please help.

create table suppliers_temp(supplier_id number(5),supplier_name varchar2(10),supplier_type varchar2(10));

table suppliers_temp created.

insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(1,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(2,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(3,'TCS','Software');
1 rows inserted.

select supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'South office'
  WHEN supplier_name = 'TCS' and supplier_type = 'Software' THEN 'East office'
  ELSE 'West Office'
END AS OFFICE
from suppliers_temp;

output is shown as follows 

SUPPLIER_ID       OFFICE
-----------       ------------
1	          North office
2	          North office
3	          East office


Thanks.
Re: trying to get two values from case stmt (2 Merged) [message #589782 is a reply to message #589772] Wed, 10 July 2013 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but how to get 'South office' also along with it.


What does this mean?
What should be the result for the data you gave?

Regards
Michel
Re: trying to get two values from case stmt (2 Merged) [message #589784 is a reply to message #589782] Wed, 10 July 2013 12:41 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
I need the output in OFFICE field as North office AND SOUTH OFFICE for supplier_name = 'IBM' and supplier_type = 'Hardware' because there are two records with those in table.Somtrhing like follows.I am not sure how to write the stmt inorder to get value in such way.

WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office' AND 'SOUTH office'
Re: trying to get two values from case stmt (2 Merged) [message #589786 is a reply to message #589784] Wed, 10 July 2013 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
how do you decide when "South Office" should be returned instead of returning "North Office?
what differentiates the two results?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: trying to get two values from case stmt (2 Merged) [message #589789 is a reply to message #589784] Wed, 10 July 2013 14:07 Go to previous message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in the sentence: "What should be the result for the data you gave?".
Post the result that should return the wanted query.

Regards
Michel
Previous Topic: EXECUTE IMMEDIATE - Create View
Next Topic: Merge statement required with Logic
Goto Forum:
  


Current Time: Fri Nov 28 02:22:53 CST 2014

Total time taken to generate the page: 0.16115 seconds