Multiple Select Statements within IF

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 do开发者_Python百科esnt seem to work. Please help!

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';

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.

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.