DATASET_TABLE Syntax - Advanced SQL Engine - Teradata Database

DATASET_TABLE ( ON
  ( DATASET_instances_retrieving_expression )
  [ USING ( { row_expression_literal | column_expression_literal } ) ]
)

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.