Oracle WHERE clause throwing ora-06502: PL/SQL: numeric or value error: number precision too large

Question

I am running into the following error when I run the below SQL

FuncTwo(ParamOne, ParamTwo) InnedColTwo, *

ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "Schema.FuncTwo", line 89

SELECT 
    C.UID AS ColOne,
    TO_NUMBER(C.ColTwo) AS ColTwo
FROM (
        SELECT
            B.UID,
            TO_NUMBER(B.MiddleColTwo) AS OuterColTwo
        FROM (
                SELECT 
                    A.UID,
                    CASE
                        WHEN (InnerColThree >= '54.6' AND InnerColThree <= '54.9') OR InnerColThree = '0' THEN 99
                        ELSE TO_NUMBER(InnerColTwo, '99.9')
                    END AS MiddleColTwo
                FROM (
                        SELECT 
                            UID,
                            FuncTwo(ParamOne, ParamTwo) InnerColTwo,
                            FuncThree(ParamOne, ParamTwo) InnerColThree
                        FROM
                            -- SOME STUFF
                        WHERE
                            -- SOME STUFF
                        UNION
                            SELECT 
                                UID,
                                FuncTwo(ParamOne, ParamTwo) AS InnerColTwo,
                                FuncThree(ParamOne, ParamTwo) AS InnerColThree
                            FROM 
                                -- SOME STUFF ...
                            WHERE 
                                -- SOME STUFF ...
                    ) A
            ) B
        WHERE B.ColTwo > 0 --Works when commented out. Throws error when uncommented.
    ) C;

But when I comment out WHERE B.ColTwo > 0 The query works. I have tried assigning number formatting to all ColTwo values with TO_NUMBER() but nothing helped.

I dont understand why the query resolves when the middle query where clause is commented out, but fails when it is uncommented.

All values for that column resolve to between 0 and 99 with no decimals or negative values or values larger than 99.

Can someone assist me in resolving this?

Answer 1

Add this in where clause Where TO_NUMBER(B.MiddleColTwo) > 0