SQL Elements (Teradata Database) - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
gxn1538171534877.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software
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.
  1. Columns from one or more tables or views in one or more databases
  2. 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*
  3. 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
  4. Arithmetic operators: +, -, *, /, MOD, **
  5. Arithmetic functions: ABS, CASE_N*, DEGREES*, EXP, LN, LOG, RADIANS*, SQRT, RANDOM, WIDTH BUCKET
  6. Trigonometric functions: COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2
  7. Hyperbolic functions: COSH, SINH, TANH, ACOSH, ASINH, ATANH
  8. CASE expressions, both valued and searched types
  9. Comparison operators: =, >, <, <>, <=, >=
  10. Logical predicates: BETWEEN, NOT BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR, NOT, LIKE, NOT LIKE, ANY and ALL
  11. Custom logical predicates: AND ALL, OR ALL (making it easier to connect a number of conditional expressions with an AND or OR operator)
  12. NULL operators: NULLIF, COALESCE, NULLIFZERO, ZEROIFNULL
  13. Built-in functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
  14. Date/Time functions: ADD_MONTHS, EXTRACT, TEMPORAL_DATE, TEMPORAL_TIMESTAMP
  15. Custom Date/Time differences and elapsed time functions
  16. Calendar fields based on a specified date column with all Teradata Calendar options.
  17. String functions: CHARACTER_LENGTH, Concatenate ( || ), LOWER, POSITION, SUBSTRING, TRIM and UPPER, as well as CHAR2HEXINT*, SOUNDEX*, TRANSLATE*, TRANSLATE_CHK*, VARGRAPHIC*
  18. Hash functions: HASHAMP*, HASHBAKAMP*, HASHBUCKET*, and HASHROW*
  19. Attribute functions: FORMAT*, OCTET_LENGTH*, TITLE* and TYPE*
  20. Built-in functions: ACCOUNT*, DATABASE*, PROFILE*, ROLE*, SESSION* and USER*
  21. 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
  22. Formulas of 1 (x), 2 (x,y), 3 (x,y,z) or any number (x1…xn) of variables (a custom feature)
  23. Free SQL Text Entry with optional arguments
  24. Free SQL Text Function with selector for functions above marked with *
  25. Subquery
  26. User Defined Functions
  27. User Defined Methods
  28. References to other variables
  29. Embedded Services User Defined Functions
  30. Geospatial Methods
  31. Table Functions
  32. Table Operators
  33. 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.
  1. Where, Having and Qualify clauses
  2. Group By clause, including Cube, Rollup, and Grouping Sets
  3. Order By clause
  4. Sample clause
  5. Top clause
  6. With Recursive clause