17.00 - Examples: GROUP BY TIME with Multiple Sources in a Query - 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

When there are multiple possible sources in a query, only PTI tables are considered when the database is inferring the timecode column. The TD_TIMECODE column is inferred by the database only when it is not ambiguous.

Example: Multiple Sources in a Query

In the example only ocean_buoys is considered when inferring the timecode, so no USING TIMECODE clause is necessary.

sel avg(a.temperature), mode(b.temperature)
from ocean_buoys a inner join ocean_buoys_nonpti b
on a.buoyid = b.buoyid
group by time(minutes(10));

Example: Invalid Example with Ambiguous Timecode Specification

In the example, TD_TIMECODE is ambiguous so it cannot be inferred and an error is reported. Because both join tables have TD_TIMECODE, USING TIMECODE is required to specify which TD_TIMECODE to use.

sel avg(a.temperature), mode(b.temperature)
from ocean_buoys a inner join ocean_buoys_seq b
on a.buoyid = b.buoyid
group by time(minutes(10));

 *** Failure 4359 Time Series: Timecode specification is ambiguous.