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.

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

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

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

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.

LVL 79

slightwv (䄆 Netminder) Commented: 2018-02-06

LVL 1

cyimxtckAuthor Commented: 2018-02-06

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

great idea!

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

Who is Participating?

Already a member? Login.