17.05 - nPath Differences on Aster Database and Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K
Aster Database nPath Advanced SQL Engine nPath
In a symbol, the Boolean expression TRUE, NOT TRUE, or integer can be enclosed in parentheses or quotation marks. In a symbol, the Boolean expression TRUE, NOT TRUE, or integer cannot be enclosed in parentheses or quotation marks.
Aggregate functions compare strings using Unicode value of each character (lexicographic order), ignoring CHARACTER SET. Aggregate functions compare strings using sort order, based on CHARACTER SET, CASESPECIFIC, and COLLATION.

AVG

Database Syntax Element Data Type Return Data Type
Aster SMALLINT, INTEGER, or BIGINT NUMERIC
  DOUBLE PRECISION, NUMERIC, or INTERVAL Same as syntax element data type
Teradata NUMERIC DOUBLE PRECISION
  INTERVAL, or DATE without TIME or TIMESTAMP Same as syntax element data type

COUNT

Database Syntax Element Data Type Return Data Type
Aster Any BIGINT
Teradata Any TD mode: INTEGER

ANSI mode: Depends on MaxDecimal value in DBSControl—see following table.

MaxDecimal Value Result Data Type Result Data Type Format
0 or 15 NUMERIC(15,0) -(15)9
18 NUMERIC(18,0) -(18)9
38 NUMERIC(38,0) -(38)9

MAX and MIN

Database Syntax Element Data Type Return Data Type
Aster Any numeric, string, or DateTime type Same as syntax element data type
Teradata Any numeric, character, DateTime or Interval data type, or BYTE If not UDT: Same as syntax element data type

UDT: Data type to which UDT is implicitly cast

SUM

Database Syntax Element Data Type Return Data Type
Aster SMALLINT or INTEGER BIGINT
  BIGINT NUMERIC
  DOUBLE PRECISION DOUBLE PRECISION
  NUMERIC or INTERVAL Same as syntax element data type
Teradata NUMERIC, INTERVAL, or DATE without TIME or TIMESTAMP Same as syntax element data type, except for NUMERIC(n,m), which returns NUMERIC(p,m), where p depends on MaxDecimal value in DBSControl—see following table.
  CHARACTER or VARCHAR DOUBLE PRECISION
MaxDecimal Value n p
0 or 15 n ≤ 15 15
  15 < n ≤ 18 18
  n > 18 38
18 n ≤ 18 18
  n > 18 38
38 Any value 38