| 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 |