Aggregates and Literal Expressions in the Select List - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Literal expressions in the select list may 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.

Literal Expression Result of Literal Expression in Query Result
Does not contain a column reference

Is a non-deterministic function, such as RANDOM

 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)

Result:

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

Result:

----------- -------------
            0             ?