17.10 - Example: GROUP BY TIME with a Derived Table - 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 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.