GROUP BY TIME and Subqueries Examples | Teradata Vantage - 17.10 - Examples: GROUP BY TIME and Subqueries - 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)

The rules and restrictions for inferring the timecode are per query, so if there are subqueries you may need to specify the USING TIMECODE clause. For more information on GROUP BY TIME rules and restrictions, see Usage Notes and for the rules and restrictions on USING TIMECODE see Usage Notes.

Example: GROUP BY TIME with Subqueries

In the example, the timecode is inferred in the inner subquery and the outer query specifies the USING TIMECODE clause:

sel avg(temperature) as a
from non_pti_tbl_seq
group by time(minutes(10))
using timecode(timecode)
having a IN ( sel count(*)
    from ocean_buoys
    group by time(minutes(1)) );

Example: Invalid GROUP BY TIME with Subqueries

In the example, the timecode is inferred in the inner subquery but not in the outer query, so an error is reported:

sel avg(temperature) as a
from non_pti_tbl_seq
group by time(minutes(10))
having a IN ( sel count(*)
    from ocean_buoys
    group by time(minutes(1)) );  

 *** Failure 4359 Time Series: GROUP BY TIME clause may not be used without a TIMECODE specification.