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.

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

William Myers

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)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

William Myers

ASKER

great idea!