Oracle Select in Case sentence

Vinum
Vinum used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Remove the & - assuming you have your variable assigned before the case statement then the case will work just fine.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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...
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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?

Author

Commented:
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...
awking00Information Technology Specialist

Commented:
If you have found another way to solve the problem, then please post the solution for the knowledge base.
Most Valuable Expert 2011
Top Expert 2012

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

if you've found a different solution, please post it
Commented:
I post it my solution directly.
When using & in front, you are asked to type in.
With this solution,  &Rapport has to be 1, 2 or 3.
The columns in the output is the same, only the filters are different.

select
*
from
(Select *
        from IALUSER.ONH_ORD_DEM_BAL_MNTH
        where &Rapport = 1 and part_no like '&Part_No')

union all

(Select *
        from IALUSER.ONH_ORD_DEM_BAL_MNTH
        where &Rapport = 2 and part_no like '&Part_No' and
             (Balance_MD_DM < 0 or Balance_MD_M1 < 0 or Balance_MD_M2 < 0 or Balance_MD_M3 < 0 or Balance_MD_Rest < 0))

union all

(Select *
        from IALUSER.ONH_ORD_DEM_BAL_MNTH
        where &Rapport = 3 and part_no like '&Part_No' and
             (JF_Lager < Salgs_Ordrer_14dage))

Author

Commented:
This worked  for mere

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial