17.05 - Example: Dynamic Row Results Returned by Specifying Table Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following example uses the extract_store_data table function to return dynamic row results in the store_data table:

     SELECT *
     FROM (TABLE(extract_store_data('…', 1000) 
     RETURNS store_data) AS store_sales;

The following equivalent examples use the sales_retrieve table function to return dynamic row results either by specifying the maximum number of output columns and their individual column names and data types or by specifying only the name of the table into which the converted rows are to be written.

     SELECT *
     FROM TABLE (sales_retrieve(9005)
     RETURNS (store    INTEGER, 
              item     INTEGER,
              quantity INTEGER)) AS s;

     SELECT *
     FROM TABLE (sales_retrieve(9005)
     RETURNS sales_table) AS s;