Avatar of cyimxtck
cyimxtck
Flag for United States of America asked on

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.
* PL/SQLOracle Database

Avatar of undefined
Last Comment
cyimxtck

8/22/2022 - Mon
slightwv (䄆 Netminder)

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

cyimxtck

ASKER
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
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cyimxtck

ASKER
great idea!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes