16.20 - Examples: GROUP BY TIME and Subqueries - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

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

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 GROUP BY TIME Rules and Restrictions and for the rules and restrictions on USING TIMECODE see USING TIMECODE Rules and Restrictions.

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.