Aggregates and Literal Expressions in the Select List

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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             ?