Home » SQL & PL/SQL » SQL & PL/SQL » Case Statement
Case Statement [message #225285] Mon, 19 March 2007 09:25 Go to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Hi,

Please tell me how can we return multiple values from Case statement.

I am using following type of case statement


select * from abc t
where t.b in (
case when t.a=1
then
b
when t.a=2
then
select b from xzy
end
);
Re: Case Statement [message #225291 is a reply to message #225285] Mon, 19 March 2007 09:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Are you looking for something like this:

SQL> select * from abc;

         A B
---------- --------------------
         1 Record 1 from abc
         2 Record 2 from abc

SQL> select * from xyz;

         A B
---------- --------------------
         1 Record 1 from xyz
         2 Record 2 from xyz

SQL> select a, case when t.a = 1 then t.b
  2     when t.a = 2 then (select b from xyz
  3                         where a = 2) end "B"
  4  from abc t;

         A B
---------- --------------------
         1 Record 1 from abc
         2 Record 2 from xyz
Re: Case Statement [message #225292 is a reply to message #225285] Mon, 19 March 2007 10:07 Go to previous messageGo to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Thanks a lot for your suggestion but I think it will not work in the following query

select
(SELECT unit_cost
FROM price_hist rh1
WHERE rh1.loc in ( case
When ps.zone_node_type= 0
then
ps.location
when ps.zone_node_type = 1
then
(select location from rpm_zone_location where zone_id=ps.zone_id)
end
) as Cost
from ps

In this case i need to return all the location associated to Zone_id and map the location with rh1.loc and then select unit_cost for them.

Please suggest
Re: Case Statement [message #225297 is a reply to message #225285] Mon, 19 March 2007 10:50 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Sample data and sample output please.

Srini
Re: Case Statement [message #225302 is a reply to message #225285] Mon, 19 March 2007 11:20 Go to previous messageGo to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
Thanks friends.

Well i tried the following query and it works

select
(SELECT unit_cost
FROM price_hist rh1
WHERE case when ps.zone_node_type= 0 then rh1.loc end =ps.location
and case when ps.zone_node_type= 1 then rh1.loc end in (select location from rpm_zone_location where zone_id=ps.zone_id)
) unit_cost
from PS ps


Thanks any way.
Re: Case Statement [message #225305 is a reply to message #225302] Mon, 19 March 2007 11:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would you use case here? Why not simply an or?
Re: Case Statement [message #225310 is a reply to message #225285] Mon, 19 March 2007 11:50 Go to previous messageGo to next message
pulkit_a
Messages: 50
Registered: December 2005
Member
I will rally be thankfull if you can tell me easier way then this..

Please suggest

Thanks
Re: Case Statement [message #225312 is a reply to message #225310] Mon, 19 March 2007 12:06 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you looking for a varying IN list, you should look at a nice reference that William Robertson has at his website

[Updated on: Mon, 19 March 2007 12:07]

Report message to a moderator

Previous Topic: Frustrating Requisition Function
Next Topic: How to do this using procedure
Goto Forum:
  


Current Time: Sun Dec 11 00:26:03 CST 2016

Total time taken to generate the page: 0.07939 seconds