case statement type logic in where clause that can return multiple values in a sub-select

Oracle's inability to use a case statement in a where clause where more than a scalar, or single value, is returned is creating issues.

We have a set of reports that run, and overnight they are simple pre-defined values.  Ad-hoc, for the user, however, is much more complicated.

Evening runs take on 5 parameters in a stored procedure that stage out data.  All five parameters = "ALL", so it is the largest possible set of data.  We tried to reconstruct this overnight run for the users, letting them decide which of the 5 values they wanted to be ALL, and individual values as well.

When we tried implementing this in the procedure, we just used a case statement in the where clause and said:

WHERE LOCATION in CASE WHEN i_parm_loc = "ALL" (select locations from table) ELSE (i_parm_loc ) END
  AND DISTRICT in
CASE
WHEN i_parm_loc = "ALL" AND i_parm_dist = "ALL" THEN (select district from table)
WHEN i_parm_loc != "ALL" AND i_parm_dist = "ALL" THEN ...(select multiples...
WHEN i_parm_loc != "ALL" AND i_parm_dist != "ALL" THEN ...(select multiples...
WHEN i_parm_loc = "ALL" AND i_parm_dist != "ALL" THEN...(select multiples...
ELSE (i_parm_dist )
four others here....

since CASE is a scalar return, we tried several ways of getting it to an array.

sys.dbms_debug_vc2coll(select multiples...)
sys.odcivarchar2list(select multiples...)

Nothing works.  We can run something simple in SQL like select sys.dbms_debug_vc2coll(1, 3, 5, 7) ct from dual and that works (12c EE), but the PL/SQL doesn't like it.

Just looking for a way for the ALL/Ad-hoc values to work and figured the CASE in the where clause was the answer.

So the user, in the end, can choose a single location value, ALL districts, single employee type, ALL markets, etc.

There HAS to be a better way than writing 10K lines of code with different queries using an IF statement to tell which one to execute?

Any help is greatly appreciated.
LVL 1
cyimxtckCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Maybe something like:
   AND 
   (
       (WHEN i_parm_loc = 'ALL' AND i_parm_dist = 'ALL' and DISTRICT in (select district from table) )
      or
       (WHEN i_parm_loc = 'ALL' AND i_parm_dist = 'SOME' and DISTRICT in (select district from table where some_col='SOME') )
      or
       ...
   )

Open in new window


Depending on your district table, you might be able to move the parameters in there to only return the correct districts and only use a single IN:

AND DISTRICT in (
      select district from table where ( 
           (i_parm_dist = 'ALL') or 
           (i_parm_dist = 'SOME'  and some_col='SOME') or
           ...
      )

Open in new window

cyimxtckCEOAuthor Commented:
That is the issue though...

(WHEN i_parm_loc = 'ALL' AND i_parm_dist = 'SOME' and DISTRICT in (select district from table where some_col='SOME')

That returns a collection, not a single row, so the CASE fails because it is scalar and expecting only one result so you get the Single Row Query returns more than one row ORA error
slightwv (䄆 Netminder) Commented:
Sorry.  I forgot to delete the WHENs.  

Do away with the CASE all together.

   
select ... from ... WHERE something = something_else
AND 
   (
       (i_parm_loc = 'ALL' AND i_parm_dist = 'ALL' and DISTRICT in (select district from table) )
      or
       (i_parm_loc = 'ALL' AND i_parm_dist = 'SOME' and DISTRICT in (select district from table where some_col='SOME') )
      or
       ...
   )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyimxtckCEOAuthor Commented:
great idea!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.