16.20 - Aggregates and Literal Expressions in the Select List - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

Literal expressions in the select list may optionally appear in the GROUP BY clause. For example, the following statement uses an aggregate function and a literal expression in the select list, and does not use a GROUP BY clause:

   SELECT COUNT(*), 
   SUBSTRING( CAST( CURRENT_TIME(0) AS CHAR(14) ) FROM 1 FOR 8 )
   FROM Sales_Table;

The results of such statements when the table has no rows depends on the type of literal expression.

IF the literal expression … THEN the result of the literal expression in the query result is …
does not contain a column reference

is a non-deterministic function, such as RANDOM

the value of the literal expression.

Functions such as RANDOM are computed in the immediate retrieve step of the request instead of in the aggregation step.

Here is an example:

SELECT COUNT(*),

SUBSTRING(CAST(CURRENT_TIME(0) AS CHAR(14))

FROM 1 FOR 8)

FROM Sales_Table;

Count(*) Substring(Current Time(0) From 1 For 8)

-------- ---------------------------------------

       0 09:01:43

contains a column reference

is a UDF

NULL.

Here is an example:

SELECT COUNT(*), UDF_CALC(1,2)

FROM Sales_Table;

   Count(*) UDF_CALC(1,2)

----------- -------------

          0             ?