16.20 - Subquery Support in PIVOT IN-List - Teradata Vantage NewSQL Engine

Teradata Vantage™ NewSQL Engine Release Summary

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Release Notes
featnum
B035-1098-162K

To avoid the overhead of issuing a separate query to generate values for input to the PIVOT IN-list clause as hard coded constants, you can now issue the query as a subquery in PIVOT IN-list. This enhancement supports subqueries that return multiple values, as opposed to returning a single value.

Benefits

  • Allows you to use a subquery instead of providing long lists of values in the PIVOT IN-list.
  • Allows you to use the subquery result for your PIVOT operation.

Considerations

PIVOT with a subquery in the IN-list is not supported in a multistatement request. PIVOT columns are decided dynamically at the optimization phase. Because of this dynamic behavior, the following are usage considerations of a PIVOT query with a subquery in the IN-list.

  • Not supported in DDL creation statements.
  • Not supported in stored procedure's cursor FETCH statement.
  • SET operations are not allowed on a PIVOT query if subquery is given in the IN-list.
  • Resultant PIVOT column names cannot be explicitly specified in the SELECT list.
  • Does not support ORDER BY clause.

Additional Information

For more information, see PIVOT in Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.