Link to home
Start Free TrialLog in
Avatar of Vinum
VinumFlag for Denmark

asked on

Oracle Select in Case sentence

I am no expert in Oracle / SQL....

In PL/SQL I want to execute a Select dependent of a variable.

I know, this example is not okay, but it illustrates what I mean:

case when &param = 1 then (select a,b,c from table)
         when &param = 2 then (select d,e,f from table)
         else (select g,h,i from table)
end

That is, when I run the sql, it prompts me for a parameter and dependend of the entered value, different values are selected from the table
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Remove the & - assuming you have your variable assigned before the case statement then the case will work just fine.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I do not think you can do what ou want this way.

If I inderstand what you are after you will need a dynamic cursor.

Check out:
http://blog.mclaughlinsoftware.com/2009/02/24/easier-way-than-nds/

If you can provide us with a little more infomration about what you want to do with the results, we can provide a more detailed answer.
SELECT CASE &param WHEN 1 THEN a WHEN 2 THEN d ELSE g END,
       CASE &param WHEN 1 THEN b WHEN 2 THEN e ELSE h END,
       CASE &param WHEN 1 THEN c WHEN 2 THEN f ELSE i END
  FROM yourtable
Avatar of Vinum

ASKER

To slightwv
In PL/SQL Developer, you can use & to prompt for a value.

Dependend on the value entered, I want to select different values from the table

My example don't work. You cannot start with Case.

New example:
Select
  case when &param = 1 then (select a,b,c from table)
           when &param = 2 then (select d,e,f from table)
           else (select g,h,i from table)
  end
from dual

Now I have the problem, that the select returns more values...
>>In PL/SQL Developer, you can use & to prompt for a value.

I understand that.  Same in sqlplus.  I just don't think you can dynamically issue select statements the way you are wanting to do without a dynamic cursor.

Did you take a look at sdstuber's proposed solution?
Avatar of Vinum

ASKER

I've requested that this question be deleted for the following reason:

It seems that this is not possible. I have found another way to solve the problem...
If you have found another way to solve the problem, then please post the solution for the knowledge base.
what you asked IS possible, a solution has already been posted.

if you've found a different solution, please post it
ASKER CERTIFIED SOLUTION
Avatar of Vinum
Vinum
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vinum

ASKER

This worked  for mere