17.10 - DATASET_TABLE Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1198-171K
Language
English (United States)
DATASET_TABLE ( ON
  ( DATASET_instances_retrieving_expression )
  [ USING ( { row_expression_literal | column_expression_literal } ) ]
)
Only the AVRO storage format uses the row_expression_literal in the USING clause. CSV does not.

Syntax Elements

Dataset_instances_retrieving_expression
A query expression, table name, or view name that results in shredding DATASET instances.
There must be at least two result columns. One column identifies a problematic DATASET instance, if encountered. The other column is the DATASET instance itself. You can include other columns that follow the DATASET instance column.
For information on creating a table, see Examples in the "Modifying DATASET Columns" section.
A typical example:
SELECT id, DatasetCol.toJson() FROM my_table ORDER BY 1;
An example with extra columns:
SELECT id, orderDataset, orderDate, orderSite FROM orderDatasetTable;
Extra columns are output as extra columns returned by the table operator.
If the Dataset_instances_retrieving_expression parameter is null, the function results in a a table with no rows.
USING
Input to the table operator as specified by Dataset_instances_retrieving_expression.
row_expression_literal
A LATIN or UNICODE string literal conforming to the dot notation syntax supported on the DATASET type. It must use the '$' prefix at the beginning, which represents the root of the DATASET instance. This parameter defines the set of data that creates the output rows.
If this parameter is null, an error occurs.
This expression applies only to the AVRO storage format. The CSV format does not use row_expression_literal.
column_expression_literal
A LATIN or UNICODE string literal conforming to the DATASET type dot notation syntax. It must use the '$' prefix at the beginning, representing the DATASET instance root, or the row expression result (depending on if the 'fromRoot' attribute is present). column_expression_literal defines output table columns, and which data populates the columns.
If this parameter is null, an error occurs.