We have used virtual columns with simple arithmetic but the column does not work properly when used in a simple SELECT statement with JOIN. Here are the column definitions of relevant fields:
"PAY_AMT" NUMBER(16,2),
"AMOUNT_RECEIVED" NUMBER(16,2),
"REMAIN_AMOUNT_DUE" NUMBER(16,2) GENERATED ALWAYS AS (NVL("PAY_AMT",0)-NVL("AMOUNT_RECEIVED",0)) VIRTUAL
This is the simple select statement that is producing incorrect results
As can be seen, the WHERE-clause has the “and d.remain_amount_due > 0” constraint in it which is getting applied properly in narrowing the rows BUT, in the projected column i.e., the SELECT list, the value returned is 0.
What could be going on here?
Server version: 19c (19.21.0.0.0)
The “d.remain_amount_due” should also return the correct value like the calculated expression i.e., “remain_amount_due_calc” column.
gk646423 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.