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

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.