Subquery works in 9i but not in 11g
Posted
by
Zsuetam
on Stack Overflow
See other posts from Stack Overflow
or by Zsuetam
Published on 2010-12-28T13:54:44Z
Indexed on
2010/12/28
14:54 UTC
Read the original article
Hit count: 280
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!
© Stack Overflow or respective owner