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

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

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             ?