DATASET_TABLE Syntax - Teradata Vantage

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 produces 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 Modifying DATASET Columns Examples.

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 produces 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. The string literal must begin with '$', 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. The string literal must begin with '$', 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.