Subquery works in 9i but not in 11g
- by Zsuetam
Statement below is working on Oracle 9i but not on Oracle 11g
SELECT *
FROM
(
SELECT 0 scrnfail_rate, '9' zz, 7 hh FROM DUAL
UNION ALL
SELECT 0 scrnfail_rate, '9' zz, 7 hh FROM DUAL
)
WHERE zz IS NOT NULL
AND TO_CHAR (hh) NOT IN
(
SELECT
DECODE
(
scrnfail_rate, 0, -1,
ROUND (LEVEL * 1 / (scrnfail_rate / 100))
-
ROUND (1 / (2 * (scrnfail_rate / 100)))
) AS nno
FROM DUAL
WHERE NVL (scrnfail_rate, 0) > 0
CONNECT BY LEVEL <= ROUND(9 * scrnfail_rate / 100)
)
It looks like Oracle 11g is ignoring where decode or even where clause in the subquery.
This query should return two rows as it does on Oracle 9i, but results ORA-01476: divisor is equal to zero on Oracle 11g EE 11.2.0.1.0 - 64bit.
Can anyone help? Thanks!
