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

Filed under:
|
|

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

Related posts about sql

Related posts about Oracle