A temporary field is a field whose value is not stored in the data source, but can be calculated from the data that is there, or assigned an absolute value. A temporary field takes up no storage space in the data source, and is created only when needed.
When you create a temporary field, you determine its value by writing an expression. You can combine fields, constants, and operators in an expression to produce a single value. For example, if your data contains salary and deduction amounts, you can calculate the ratio of deductions to salaries using the following expression:
deduction / salary
You can specify the expression yourself, or you can use one of the many supplied functions that perform specific calculations or manipulations. In addition, you can use expressions and functions as building blocks for more complex expressions, as well as use one temporary field to evaluate another.
You can use two types of temporary fields (a virtual field and a calculated value), which differ in how they are evaluated:
A virtual field (DEFINE) is evaluated as each record that meets the selection criteria is retrieved from the data source. The result of the expression is treated as though it were a real field stored in the data source.
A calculated value (COMPUTE) is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed. Therefore, the calculation is performed using the aggregated values of the fields.
The following illustration shows how a request processes, and when each type of temporary field is evaluated:
In the following example, both the DRATIO field (virtual field) and the CRATIO (calculated value) use the same expression DELIVER_AMT/OPENING_AMT, but do not return the same result. The value for CRATIO is calculated after all records have been selected, sorted, and aggregated. The virtual field DRATIO is calculated for each retrieved record.
DEFINE FILE SALES DRATIO = DELIVER_AMT/OPENING_AMT; END TABLE FILE SALES SUM DELIVER_AMT AND OPENING_AMT AND DRATIO COMPUTE CRATIO = DELIVER_AMT/OPENING_AMT; END
The output is:
The following is to help you choose the kind of temporary field you need.
Choose a virtual field when you want to:
Choose a calculated value when you want to: