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 my_file.pc:
Error at line 4487, column 42 in file my_file.pc            AND             t.amount = (:db_inamount - (SELECT NVL(overrun_amount,0)

What about using AND t.amount = (SELECT :db_amount - NVL(overrun_amount, 0) ... — OMG Ponies
– OMG Ponies, Commented Dec 30, 2009 at 6:58
@OMG, It worked. Could you please explain me the teason behind this — Sachin
– Sachin, Commented Dec 30, 2009 at 7:16
Try to give more informative titles than "Compile time Error in <programming language>". — Andrew Grimm
– Andrew Grimm, Commented Oct 23, 2011 at 22:04

OMG Ponies · Accepted Answer · 2009-12-31 08:39:08Z

2

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.

answered Dec 31, 2009 at 8:39

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sachin Over a year ago

Yes thats true, but I want to know why AND t.amount = (SELECT :db_amount - NVL(overrun_amount, 0) worked and (:db_inamount - (SELECT NVL(overrun_amount,0) from table ) doesnot.

Gary Myers Over a year ago

The latter is a scalar subquery expression, which was introduced back in 8i. But those 'modern' features aren't understood by the SQL parser in the pre-compiler. OMG's solution is a simpler subquery syntax supported since wayback.

Collectives™ on Stack Overflow

Compile time Error in ProC (Embedded sql in C)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related