17.10 - Example: Aggregating Multiple Values from Multiple Rows Into One Instance - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - DATASET Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1198-171K
Language
English (United States)

Use DATASET_PUBLISH to aggregate multiple values from multiple rows into one instance locally on each AMP. 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"
}]