Multiple Select Statements within IF

Question

I could use some help to write a prog./sql construct for a report..

The Sql should first check for the prompt & then decide which "select" statements should run

something like (pseudo-code)

Select ACCT,LOC FROM (

   IF :loc = 'MN' THEN
      Select acc as ACCT,location as LOC
   ELSE IF :loc = 'MA' THEN
      Select accid as ACCT,locid as LOC
   ELSE IF :loc = 'PA' THEN 
      Select accountid as ACCT,location as LOC
   END IF)

the above doesnt seem to work. Please help!

Answer 1

You could do this:

Select acc as ACCT,location as LOC 
where :loc = 'MN' 
UNION ALL
Select accid as ACCT,locid as LOC 
where :loc = 'MA' 
UNION ALL
Select accountid as ACCT,location as LOC 
where :loc = 'PA';

Answer 2

If you can create custom PL/SQL functions, then you can return result set from it (so called table of records) and then just select from function. See this article.

Answer 3

How about ...

select acc,
       case :loc
       When 'MN' then location
       When 'MA' then locid
       When 'PA' then location
       ...
       end
from   ...

or ...

select acc,
       case when :loc in ('MN','PA')
            then location
            When :loc in ('MA',...)
            then locid
            ...
       end
from   ...

It would be more friendly to do that because it reduces the number of unique cursors.

Answer 4

How about creating a factory class that does this for you.