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.
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:
Open in new window