Avatar of Vinum
Vinum
Flag 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
Oracle Database

Avatar of undefined
Last Comment
Vinum

8/22/2022 - Mon
Gerwin Jansen

Remove the & - assuming you have your variable assigned before the case statement then the case will work just fine.
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.
Sean Stuber

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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...
slightwv (䄆 Netminder)

>>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?
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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
awking00

If you have found another way to solve the problem, then please post the solution for the knowledge base.
Sean Stuber

what you asked IS possible, a solution has already been posted.

if you've found a different solution, please post it
ASKER CERTIFIED SOLUTION
Vinum

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vinum

ASKER
This worked  for mere
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy