The Variable Creation function makes possible the creation of variables as columns in a table or view. The user creates each new variable as an expression by selecting various SQL keywords and operators as well as table and column names. SQL keywords and operators allowed include arithmetic and logical operators, date/time operators, the typical aggregation functions, as well as the newer ordered analytical (windowed OLAP) functions. The only typing normally required is the typing of names, descriptions and values (although some automation is provided for names and values).
In addition to defining variables as expressions or formulas, the user may specify constraints on the data, either for all the variables defined in a Variable Creation function, or on an individual basis. Table level constraints defined for all variables result in WHERE, HAVING or QUALIFY clauses in the generated SQL. Constraints defined for individual variables result in the use of CASE clauses in order to allow for different constraints on different variables in the same SQL statement. A feature to allow the creation of numerous similar variables using constraints based on specific values of one or more ‘dimensioning’ columns is also provided.
- Variables derived in a single table must have the same aggregation type and level.
- A number of tables may be referenced by the variables defined in a single Variable Creation function.
- Variables referenced by another variable must not be dimensioned.
- All the variables in a Variable Creation function share the same table level constraints.
- The user may request at any time that the intermediate table created by a Variable Creation function be validated using the Teradata EXPLAIN feature.
The standard result options are available with the Variable Creation function, namely Select, Explain Select, Create Table and Create View. The choice depends primarily on whether this analysis produces a final result or an intermediate result, and if so, whether the user wants to create a permanent table or view for this intermediate result. If a permanent result is not desired, the Select option can be used to view and verify results.
Even if this analysis produces an intermediate result directly referred to by another analysis, the Select option can still be used since a volatile table will automatically be created in this case to allow the referring analysis to access the results.