17.10 - Examples: GROUP BY TIME with Multiple Sources in a Query - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
Language
English (United States)

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.