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
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.
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.