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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.