16.20 - Examples: GROUP BY TIME and UNION - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)
Last Update
2019-05-02

The following examples again show the rules for inferring the timecode are per query, this time showing this rule with UNIONs.

Example: GROUP BY TIME with UNION

The following example shows that the GROUP BY TIME queries inside the union do not need USING TIMECODE clauses because the timecode can be inferred in each subquery in these particular examples.

select Q.avrg, Q.md, Q.grp
from
(
 sel avg(temperature) as avrg, mode(temperature) as md, 1 as grp
 from OCEAN_BUOYS
 group by time(minutes(5) and buoyid)

 union

 sel avg(temperature) as avrg, mode(temperature) as md, 0 as grp
 from OCEAN_BUOYS
 group by time(minutes(10) and buoyid)
) as Q
order by 1;

Example: Invalid GROUP BY TIME with UNION

The example shows there is no timecode to infer for the query after the UNION:

select Q.avrg, Q.md, Q.grp
from
(
 sel avg(temperature) as avrg, mode(temperature) as md, 1 as grp
 from ocean_buoys
 group by time(minutes(5) and buoyid)

 union

 sel avg(temperature) as avrg, mode(temperature) as md, 0 as grp
 from ocean_buoys_nonpti
 group by time(minutes(10) and buoyid)
) as Q

order by 1;

*** Failure 4359 Time Series: GROUP BY TIME clause may not be used without an implicit or explicit TIMECODE specification