Example: GROUP BY TIME with a Derived Table - Analytics Database - Teradata Vantage

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)); 

Result:

*** 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);

Result:

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