CODEWITHSUNDEEP
oracle-databaseexpressionbusiness-intelligenceobieefusion
besho_Demian
besho_Demian

Reputation: 31

Error in Oracle BI expression

I am trying to check if the field is 0 or null and if so, the remaining amount will be equal the invoice amount.

This error appears and I searched for it but I found nothing!

enter image description here

Here is the code:

IF (G_Invoice.Amount_Paid = 0 OR G_Invoice.Amount_Paid is null )

then

Remaining_Amount := G_Invoice.Invoice_Amount

else

G_Invoice.Invoice_Amount-G_Invoice.Amount_Paid

end if;

Upvotes: 0

Views: 58

Answers (2)

Victor HDC
Victor HDC

Reputation: 555

If the measure is being calculated in Oracle BI, the LSQL code should be:

Remaining_Amount - ifnull(G_Invoice.Amount_Paid,0)

Upvotes: 1

user5683823
user5683823

Reputation:

The error is easy to explain (I did so in a brief Comment).

But the same result may be obtained much more simply:

Remaining_Amount := G_Invoice.Invoice_Amount-NVL(G_Invoice.Amount_Paid, 0)

No need for an IF statement (that is hidden within NVL). No need to treat the case of the paid amount being zero as a special case (if you subtract 0, that is the same as returning the INVOICE_AMOUNT unchanged). NVL will cause a null to be converted to zero.

Upvotes: 0

Related Questions