NVL (EXPR1,0) vs NVL(EXPR1,-1)

Question

MERGE INTO A
USING (  
    SELECT B_1,B_2,B_3,SUM(B_4)
    FROM B
    GROUP BY B_1,B_2,B_3) srt
ON (
    A.A_1 = B.B_1
    AND NVL (A.A_2, 0) =
            NVL (B.B_2, 0)
    AND NVL (A.A_3, ' ') =
            NVL (B.B_3, ' ') )
WHEN MATCHED
THEN   'DML STATEMENT';

Above throws an error

Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-30926: unable to get a stable set of rows in the source tables

But when you replace the above query with below NVL(EXPR, -1) it works without any error. Any reason one can foresee I am unable to see?

MERGE INTO A
USING (  
    SELECT B_1,B_2,B_3,SUM(B_4)
    FROM B
    GROUP BY B_1,B_2,B_3) srt
ON (
    A.A_1 = B.B_1
    AND NVL (A.A_2, -1) =
            NVL (B.B_2, -1)
    AND NVL (A.A_3, ' ') =
            NVL (B.B_3, ' ') )
WHEN MATCHED
THEN   'DML STATEMENT';

That is strange. What are the datatypes for the columns? Can you show the CREATE TABLE statements for both tables? Have you tried this outside Java? — Thorsten Kettner, Feb 16 '17 at 07:39
*_1 and *_3 is varchar and *_2 is number. I tried this in stored procedure called from a java program. — Rohan K, Feb 16 '17 at 07:44
The only flaw I can spot is that you access `B` in the ON clause where `B` cannot be known; it should be `srt`. — Thorsten Kettner, Feb 16 '17 at 08:17

Ram Limbu · Answer 1 · 2017-02-16T08:41:21.157

Though your two queries looks similar, they may behave differently due to NVL function used in ON clause Columns. Analyzing above two queries and their results, what I concluded is that either of Columns A.A_2 and B.B_2 must have values both NULL and 0(but not the value -1).

To resolve "unable to get a stable set of rows in the source tables", you have to use the same NVL function in GROUP BY sub-query that is being used in ON clause.

NVL (EXPR1,0) vs NVL(EXPR1,-1)

1 Answers1