16.20 - Example: Aggregating Multiple Values from Multiple Rows Into One Instance - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ DATASET Data Type

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
pgs1512082344257.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Use DATASET_PUBLISH to aggregate multiple values from multiple rows into one instance locally on each AMP within Teradata. This AMP-local aggregation is standard operating procedure for table operators.

DATASET_PUBLISH may be invoked twice within a statement to perform a single aggregation of all input values. If DATASET_PUBLISH is invoked once without the PARTITION BY clause, each AMP produces one row of aggregated output. Some input data is added to the source table in this example.

INSERT INTO employeeTable(4,'Jose Hernandez','Engineering'); 
INSERT INTO employeeTable(5,'Kyle Newman','Engineering');
INSERT INTO employeeTable(6,'Pamela Giles','Sales');

SELECT data.toJSON() FROM DATASET_PUBLISH
(
	ON (SELECT * FROM employeeTable)	 
) AS avroFiles;

data.toJSON()
[{
	"empID": 5,
	"empName": "Kyle Newman",
	"empDept": "Engineering"
},
{
	"empID": 3,
	"empName": "Steven Mazzo",
	"empDept": "Engineering"
},
{
	"empID": 1,
	"empName": "George Smith",
	"empDept": "Accounting"
},
{
	"empID": 2,
	"empName": "Pauline Kramer",
	"empDept": "HR"
}]
–-------------------------------------
[{
	"empID": 4,
	"empName": "Jose Hernandez",
	"empDept": "Engineering"
}]
–-------------------------------------
[{
	"empID": 6,
	"empName": "Pamela Giles",
	"empDept": "Sales"
}]