16.20 - Example: GROUP BY TIME with a Derived Table - 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 following example again shows the rules and restrictions for inferring the timecode are per query, this time showing an inner and an outer query. The GROUP BY TIME query in the inner query does not need a USING TIMECODE clause because there is a single PTI table from which to infer the timecode column. However, the outer GROUP BY TIME query needs a USING TIMECODE clause because the derived table (min_temp) is not a PTI table, so no timecode column can be inferred:

SELECT AVG(min_temp)
FROM
(
  SELECT MIN(temperature)
  FROM OCEAN_BUOYS
  GROUP BY TIME(minutes(1) AND BUOYID) 
) AS "inner" (min_temp)
GROUP BY TIME(minutes(10)); 

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

The following example uses the USING clause to specify the TD_TIMECODE derived from a derived table for the outer block:

SELECT AVG(min_temp)
FROM
(
   SELECT MIN(temperature), BEGIN($td_timecode_range) d1
   FROM OCEAN_BUOYS
   GROUP BY TIME(minutes(1))
) AS "inner" (min_temp, td_timecode)
GROUP BY TIME(minutes(10)) USING TIMECODE(td_timecode);

*** Failure 3706 Syntax error: 
Virtual columns $TD_TIMECODE_RANGE and $TD_GROUP_BY_TIME are not allowed in the GROUP BY TIME clause.