17.00 - Examples: GROUP BY TIME and UNION - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

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