15. VIFRED Field Specifications : Derived Fields : Guidelines for Specifying Derivation Formulas : Aggregates in Derivation Formulas
 
Share this page                  
Aggregates in Derivation Formulas
You can use aggregate functions in derivation formulas for simple fields, but not in derivation formulas for table field columns. To derive a value for a simple field based on an aggregate of a table‑field column, use the following syntax:
aggfunction (tablefieldname[*].columnname)
This table lists the aggregates that can be used in derivation formulas, the data types that can be used with each aggregate, and the data type of the derived field:
Aggregate
Data type of
source field:
Data type of
derived field:
count
any
integer
sum
integer
float
money
date (intervals only)
same as source
avg
integer
float
money
date (intervals only)
same as source except for integer
max
any
same as source
min
any
same as source
If a derivation formula contains a reference to an aggregate of a table field column, and the table field contains invalid values or is empty (has no values), the value of the aggregate is:
• Zero (0), if the aggregate is count
• Null, if the aggregate is avg, sum, max, or min
If the formula contains references to other fields that contain invalid values, VIFRED blanks out the derived field.