Scenario: When we add formula within the saved which generates the final output with the application of multiple variables. The sample formula is provided below:
( CASE WHEN {type} IN (‘Sales Order’) AND {quantity} <> 0 THEN {amount} – ({quantity} * {custcol_aha_sales_invoice_cost}) ELSE 0 END) / NULLIF( ( CASE WHEN {type} IN (‘Sales Order’) AND {quantity} <> 0 THEN NVL({amount}, 0) ELSE 0 END ), 0)
However, the final output will not be generated correctly if the variable(quantity, custcol_aha_sales_invoice_cost etc) does not have value in it.
Solution
The application of NVL could hinder the final output, by the usage of zero in null value and the updated formula is provided below.
NVL(( CASE WHEN {type} IN (‘Sales Order’) AND {quantity} <> 0 THEN NVL({amount}, 0) – (NVL({quantity},0) * NVL({custcol_aha_sales_invoice_cost},0)) ELSE 0 END),0) / NULLIF( ( CASE WHEN {type} IN (‘Sales Order’) AND {quantity} <> 0 THEN NVL({amount}, 0) ELSE 0 END ), 0)