Compile time Error in ProC (Embedded sql in C)

Question

Please tell me whats wrong with the query. I am using Pro C.

EXEC SQL SELECT 1
        INTO    :db_count
        FROM    sachin t
        WHERE   t.serialno =   :serial_no
        AND     t.amount = (:db_inamount - (SELECT NVL(overrun_amount,0)
                                            FROM  sunny tovrun
                                            WHERE tovrun.serialno = :serial_no
                                            AND   tovrun.timestamp = t.timestamp
                                            AND   rownum < 2)
                            )
        AND     t.request_code = 11
        AND     t.reason_code = 0
        AND     t.reversed    = 0
        AND rownum < 2;

And getting the compilation errors

Syntax error at line 4487, column 42, file at_txnlog.pc:
Error at line 4487, column 42 in file at_txnlog.pc
        AND             t.amount = (:db_inamount - (SELECT NVL(overrun_amount,0)

.........................................1
PCC-S-02201, Encountered the symbol "NVL" when expecting one of the following:

   ( ) * + - / . @ | at, day, hour, minute, month, second, year,
The symbol "(" was substituted for "NVL" to continue.

Syntax error at line 4488, column 14, file at_txnlog.pc:
Error at line 4488, column 14 in file at_txnlog.pc

Answer 1

Use:

 AND t.amount = (SELECT :db_amount - NVL(overrun_amount, 0) ...

It's a standard computed column, where the value is calculated prior to the comparison to the t.amount value for equality.