Examples: Flag Setting Combinations - Advanced SQL Engine - Teradata Database

Example 1: One-Dimensional Array With One Null Element

/*Oracle-compatible and TD syntax respectively: */
Create Type intarray as VARRAY(3) OF Int;
Create Type intarray as Int Array[3];

Create Table tab1 (id int, phonenumber intarray);
Ins into tab1(10, NEW intarray(111222333, NULL, 123456789));
Sel phonenumber from tab1;

For both YYY and NNY, the parcel body fields of the data parcels for the select query contain:

1. The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".
2. The length field is a 2-byte SMALLINT of 17.
3. The Current Cardinality field is a 4-byte unsigned integer of 3.
4. The Elements-Null-Indicator Bits is (3+7)/8= 1 byte and appears as "01000000", because the second element is NULL.
5. The Array Elements Data is the binary representation of integer 111222333, followed by the dummy null value of integer zero, followed by integer 123456789.

Example 2: One-Dimensional Array With UDT Element Type

This example is a one-dimensional array with a UDT element type with a null attribute.

CREATE TYPE employee AS (name VARCHAR(10),
                         employee_id INTEGER)
...;
/*Oracle-compatible and TD syntax respectively: */
CREATE TYPE emparray AS VARRAY(20) OF employee;
CREATE TYPE emparray AS employee ARRAY[20];
Create Table tab2 (dept_no int, dept_emps emparray);
Ins into tab2(10, NEW emparray (NEW employee('Mike', NULL), NEW
employee('Mark', 101)));
Sel dept_emps from tab2;

For YYY, the parcel body fields of the data parcels for the select query contain:

1. The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".
2. The length field is a 2-byte SMALLINT of 25.
3. The Current Cardinality field is a 4-byte unsigned integer of 2.
4. The Elements-Null-Indicator Bits is (3+3+7)/8= 1 byte and appears as "00100000", because the second attribute of the first element is NULL.
5. The Array Elements Data is the binary representation of string 'Mike', followed by the dummy NULL value of integer zero, followed by string 'Mark' which is followed by integer 101.

For NNY, assuming NEW employee ('Mike', NULL) is transformed to 'Mike-NULL' and NEW employee ('Mark', 101)) is transformed to 'Mark-101', the parcel body fields of the data parcels for the select query contain:

1. The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".
2. The length field is a 2-byte SMALLINT of 26.
3. The Current Cardinality field is a 4-byte unsigned integer of 2.
4. The Elements-Null-Indicator Bits is (2+7)/8= 1 byte and appears as "00000000".
5. The Array Elements Data is the binary representation of string 'Mike-NULL', followed by string 'Mark-101'.

Example 3: Multidimensional Array With One Null Element

/*Oracle-compatible and TD syntax respectively: */
Create Type TwoD_intarray as VARRAY(2)(2) OF Integer;
Create Type TwoD_intarray as Integer Array[2][2];

Create Table tab3 (id int, simulation TwoD_intarray);
Ins into tab3(10, NEW TwoD_intarray(111222333, 123456789, NULL, 777777777));
Sel simulation from tab3;

For both YYY and NNY, the parcel body fields of the data parcels for the above query contain:

1. The null indicator bit array is (n+7)/8 = (1+7)/8 = 1 byte and appears as "00000000".
2. The length field is a 2-byte SMALLINT of 21.
3. The Current Cardinality field is a 4-byte unsigned integer of 4.
4. The Elements-Null-Indicator Bits is (4+7)/8= 1 byte and appears as "00100000".
5. The Array Elements Data is the binary representation of integer 111222333, followed by integer 123456789, followed by the dummy null value of integer zero which is followed by integer 777777777.