The Variable Creation function allows the creation of new columns or variables as SQL expressions or formulas based on the features, functions and operators outlined below, dependent on the release of Teradata in use at the time the variables are defined.
The same list applies to the creation of Dimensions, with the exclusion of all Aggregation functions and Ordered Analytical functions.
- Columns from one or more tables or views in one or more databases
- Aggregation functions: MIN, MAX, SUM, AVG, COUNT, CORR, COVAR_POP/SAMP, STDDEV_POP/SAMP, VAR_POP/SAMP, SKEW, KURTOSIS, REGR_INTERCEPT/SLOPE/R2, REGR_AVGX*, REGR_AVGY*, REGR_COUNT*, REGR_SXX*, REGR_SXY*, REGR_SYY*
- Ordered Analytical functions Windowed AVG, COUNT, MAX, MIN, and SUM, along with PERCENT_RANK, RANK and ROW NUMBER, in addition to updated equivalents of the older style OLAP functions MDIFF and QUANTILE, along with MLINREG in its original legacy form
- Arithmetic operators: +, -, *, /, MOD, **
- Arithmetic functions: ABS, CASE_N*, DEGREES*, EXP, LN, LOG, RADIANS*, SQRT, RANDOM, WIDTH BUCKET
- Trigonometric functions: COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2
- Hyperbolic functions: COSH, SINH, TANH, ACOSH, ASINH, ATANH
- CASE expressions, both valued and searched types
- Comparison operators: =, >, <, <>, <=, >=
- Logical predicates: BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR, NOT, LIKE, NOT LIKE, ANY and ALL
- Custom logical predicates: AND ALL, OR ALL (making it easier to connect a number of conditional expressions with an AND or OR operator)
- NULL operators: NULLIF, COALESCE, NULLIFZERO, ZEROIFNULL
- Built-in functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
- Date/Time functions: ADD_MONTHS, EXTRACT, TEMPORAL_DATE, TEMPORAL_TIMESTAMP
- Custom Date/Time differences and elapsed time functions
- Calendar fields based on a specified date column with all Teradata Calendar options.
- String functions: CHARACTER_LENGTH, Concatenate ( || ), LOWER, POSITION, SUBSTRING, TRIM and UPPER, as well as CHAR2HEXINT*, SOUNDEX*, TRANSLATE*, TRANSLATE_CHK*, VARGRAPHIC*
- Hash functions: HASHAMP*, HASHBAKAMP*, HASHBUCKET*, and HASHROW*
- Attribute functions: FORMAT*, OCTET_LENGTH*, TITLE* and TYPE*
- Built-in functions: ACCOUNT*, DATABASE*, PROFILE*, ROLE*, SESSION* and USER*
- Miscellaneous other SQL constructs, including: Asterisk(*), BYTES, CAST, Expand Column Reference, Expression with Alias, Expression with Order By, New Variant Type, Parentheses ( ), Sample ID and SQL element list
- Formulas of 1 (x), 2 (x,y), 3 (x,y,z) or any number (x1…xn) of variables (a custom feature)
- Free SQL Text Entry with optional arguments
- Free SQL Text Function with selector for functions above marked with *
- Subquery
- User Defined Functions
- User Defined Methods
- References to other variables
- Embedded Services User Defined Functions
- Geospatial Methods
- Table Functions
- Table Operators
- Stored Procedures
* functions provided by the SQL Text Function via a selector, not as elements
The Variable Creation function also allows the creation of various expert clauses, some with specialized elements. In general, the elements listed above can be used in expert clauses, with some limitations. For example, Aggregation functions may only be used in a Having clause, and ordered analytical functions may only be used in a Qualify clause.
- Where, Having and Qualify clauses
- Group By clause, including Cube, Rollup, and Grouping Sets
- Order By clause
- Sample clause
- Top clause
- With Recursive clause