CASE in Interactive Report (APEX)

In APEX 3.1.2 there is a problem with the CASE statement when you use it in the Compute function of an Interactive Report. You might see following error:


The above issue got fixed in APEX 3.2, as you can see here. So if you want that functionality, it's worth upgrading to APEX 3.2. There are more smaller fixes in like these and a number of security enhancements, so I recommend to use APEX 3.2 if you can.

But... when you try this CASE statement (use of smaller than, larger than or BETWEEN) you get an Invalid computation expression message.


Joel Kallman told me this is a bug (Bug 8347332) and it will be fixed in the APEX 3.2.1 patch set. The Apex development team was too restrictive in the operators they allow inside this type of expression.

If I can ask for one more enhancement: let us also be able to use Enters, because if I type some more when clauses it becomes a very long string. It would be nice if I could structure it like in the next screenshot. It would also nice if we could make that Computation field resizeable. As Patrick is now in the team, he already did that in his ApexLib and as JQuery will be in APEX, I hope to see it in 4.0 ;-) Thanks so much!


A workaround at the moment is to include the case statement directly in your SQL statement.
If you can't do that (like our client wanted to specify the above dynamic), you need to build it yourself or search for an alternative function that does work.