Example: Using Dot Notation with the DATASET Data Type - Advanced SQL Engine - Teradata Database

Example: Using the Table Myavrotable09 (AVRO):

/*simple named references on all rows in each instance*/

SELECT avroFile.Sale."Item_ID" 
FROM Myavrotable09;
> 55

/*recursive descent operator on all rows in each instance*/

SELECT avroFile.."Item_ID" 
FROM Myavrotable09;
> 55

/*wildcard operator on all rows in each instance*/

SELECT avroFile.Sale.* 
FROM Myavrotable09;

> [55,"bicycle","red","boys",1,100.0,100.0]

/*named index list on all rows in each instance*/ 

SELECT avroFile.Sale["Item_ID","Item_Name"]
FROM Myavrotable09;
> [55,"bicycle"]

Example: Using the Table csv_table (CSV):

In this example, insert the following CSV data into the myCSVTable09 table. The first line contains the header, columns and records delimited by the default character.

ItemNum,ItemName,Quantity,Price

100,Paint Brush,3,7.99

101,Roller,5,12.99

102,Bucket,7,15.99

The CSV data is an array of records.

/* The following SELECT statement does not use the recursive descent operator or the array syntax. Therefore, nothing is selected and null is returned. */
SELECT csv.ItemName from csv_table;

Result:

> ?

/* The field names are case sensitive, so in this example, "price" does not match up with the field name of "Price". Therefore, the following query returns null.*/
SELECT csv[*].price from csv_table;

Result:

> ?

/* Select a field from every record using the recursive descent operator. */
SELECT csv..ItemName from csv_table;

Result:

> ["Paint Brush","Roller","Bucket"]

/* Select two fields from every record using the recursive descent operator. */
SELECT csv..[ItemID,ItemName] from csv_table;

Result:

> ["100","Paint Brush","101","Roller","102","Bucket"]

/* Same as the previous example, but select the fields in a different order. */
SELECT csv..[ItemName,ItemID] from csv_table;

Result:

> ["Paint Brush","100","Roller","101","Bucket","102"]

/* Select record number 2 (all fields). */ 
SELECT csv[2] from csv_table;

Result:

> ["102","Bucket","7","15.99"]

/* Select all records (all fields). */
SELECT csv[*] from csv_table;

Result:

> ["100","Paint Brush","3","7.99","101","Roller","5","12.99","102","Bucket","7","15.99"]

/* Select a field from every record using the array syntax. */
SELECT csv[*].Price from csv_table;

Result:

> ["7.99","12.99","15.99"]

/* Select out a field from record number 1. */
SELECT csv[1].ItemName from csv_table;

Result:

> Roller

/* Select all fields from record number 1. */
SELECT csv[1].* from csv_table;

Result:

> ["101","Roller","5","12.99"]

/* Select record 0 and record 2 (all fields) using the index list operator. */
SELECT csv[0,2] from csv_table;

Result:

> ["100","Paint Brush","3","7.99","102","Bucket","7","15.99"]

/* Select a field from record 0 and record 2 using the index list operator. */
SELECT csv[0,2].ItemName from csv_table;

Result:

> ["Paint Brush","Bucket"]

/* Select out record number 0 and 2 using the array slice operator with a step value of 2 (so that record number 1 is skipped). */
SELECT csv[0:3:2] from csv_table;

Result:

> ["100","Paint Brush","3","7.99","102","Bucket","7","15.99"]

/* Select out a field from record number 0 and 2 using the array slice operator with a step value of 2. */
SELECT csv[0:3:2].Price from csv_table;

Result:

> ["7.99","15.99"]