The VALUE function is the substitution of a nonnull value (specified in the second argument) for each null value found in the column designated by the first argument. You must specify two or more arguments, and the data types of the arguments must be comparable. The following SQL statement selects a column that contains null values. When you run this statement:
SELECT COMM FROM Q.STAFF WHERE YEARS = 5
QMF produces the following report:
+--------------------------------------------------------------------------------+ | COMM | | ---------- | | - | | 206.60 | | - | | 806.10 | | 188.00 | +--------------------------------------------------------------------------------+
When you use VALUE with a second argument of 0 in the SELECT clause of an SQL statement, the null values are replaced with 0.00 because the data type is DECIMAL. For example, when you run this statement:
SELECT VALUE(COMM, 0) FROM Q.STAFF WHERE YEARS = 5
QMF produces this report:
+--------------------------------------------------------------------------------+ | COMMISSION | | ---------------- | | 0.00 | | 206.60 | | 0.00 | | 806.10 | | 188.00 | +--------------------------------------------------------------------------------+