Oracle DECODE statement failing on RHEL but works on HPUX

Oracle DECODE statement failing on RHEL but works on HPUX

I’ve isolated a very specific code piece that works on our HP-UX Oracle 11.2 environment, but fails on our RHEL 7.1 Oracle 11.2 environment. Any pointers as to why this would happen?
Everything else (except PSU level) is generally the same.
TIA
AND v_effective_date
BETWEEN DECODE
(pet.attribute1,’OVERTIMEVACCOMP’,
fnd_date.canonical_to_date(prv2.result_value),
TO_DATE (’01/01/0001′, ‘DD/MM/YYYY’))
AND DECODE
(pet.attribute1,’OVERTIMEVACCOMP’,
fnd_date.canonical_to_date(prv3.result_value),
TO_DATE (’01/01/0001′, ‘DD/MM/YYYY’))

Solutions/Answers:

Solution 1:

Most Entity-Attribute-Value models have a fatal flaw: stringly-typed data.

If all values are stored as strings it’s critical that attribute filtering occurs before those values are converted into a type. But Oracle’s query optimizations make it almost impossible to enforce a specific order of operations in SQL.

This question has a simple example of how bizarre this out-of-order execution can get. It’s a bit extreme, but will hopefully help you prove how unpredictable order of operations can be. You wouldn’t think this query could fail, but it does:

WITH data AS (SELECT 1 AS cond, 10 AS num, 0 AS div FROM DUAL)
SELECT
  CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE SUM(num / div) END)
       ELSE -1
  END AS result
FROM data
GROUP BY cond;

ORA-01476: divisor is equal to zero

We don’t know exactly how Oracle implements the order. Maybe it’s different between RHEL and HPUX, maybe it’s different on Thursdays. Unfortunately, even using a LEAST may not be bullet-proof. That function may logically operate in order, and it may normally use short-circuit evaluation, but it’s not guaranteed to always run in that order. You may have just switched between one 99.9% solution to another 99.9% solution.

There are only two fool-proof solutions to this, discussed in more detail in my answer here. Either change the table to use a different column for different types or add an inline view with a ROWNUM to every query. Neither of which is pleasant.

References