17.10 - 例: DATASETデータ型にドット表記の使用 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - DATASETデータ型

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1198-171K-JPN
Language
日本語 (日本)

例: テーブル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"]

例: テーブルcsv_table(CSV)の使用

この例では、次のCSVデータをmyCSVTable09テーブルに挿入します。 最初の行にはデフォルトの文字で区切られたヘッダー、列、およびレコードが含まれています。

ItemNum,ItemName,Quantity,Price

100,Paint Brush,3,7.99

101,Roller,5,12.99

102,Bucket,7,15.99

CSVデータはレコードの配列です。

/* 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;

結果:

> ?
/* 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;

結果:

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

結果:

> ["Paint Brush","Roller","Bucket"]
/* Select two fields from every record using the recursive descent operator. */
SELECT csv..[ItemID,ItemName] from csv_table;

結果:

> ["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;

結果:

> ["Paint Brush","100","Roller","101","Bucket","102"]
/* Select record number 2 (all fields). */ 
SELECT csv[2] from csv_table;

結果:

> ["102","Bucket","7","15.99"]
/* Select all records (all fields). */
SELECT csv[*] from csv_table;

結果:

> ["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;

結果:

> ["7.99","12.99","15.99"]
/* Select out a field from record number 1. */
SELECT csv[1].ItemName from csv_table;

結果:

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

結果:

> ["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;

結果:

> ["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;

結果:

> ["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;

結果:

> ["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;

結果:

> ["7.99","15.99"]