16.20 - Examples: GROUP BY TIME with Multiple Sources in a Query - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

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

When there are multiple possible sources in a query, only PTI tables are considered when Teradata Database is inferring the timecode column. The TD_TIMECODE column is inferred by Teradata 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.