Oracle APEX - Interactive Report Region and CASE SQL Issues

Recently when defining some Human Capital Management System (HCMS) Compensation Planning and Metrics Reports in Oracle Application Express I discovered a problem with using certain Oracle CASE Statement Logic.

In defining compound statements both AND and IF are not supported in the APEX Interactive Report Region Environment.

This shortfall makes developing compound CASE statements more of a challenge which should not be necessary. A semi-intuitive work around does exist though - specifically nesting WHEN statements as shown in the example below.

CASE WHEN (B*12) >= I THEN
  CASE WHEN (B*12) < J
     THEN 1 ELSE 0 
   END
ELSE 0
END

Ideally the work around shown above would not be necessary and all CASE SQL statement operators would be supported.

This would also make the underlying code more readable. Hopefully in the next release the APEX IRR Compute environment will better support Oracle CASE SQL.