Example: Aggregating Multiple Values from Multiple Rows Into One Instance

Teradata Vantage™ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K

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"
}]