Examples: GROUP BY TIME and UNION - Analytics Database - Teradata Vantage

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;

Result:

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