Example: Multiple Input Matrixes, One SQL Statement - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
fsi1592016213432.ditamap
dita:ditavalPath
fsi1592016213432.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

In the following example, table T has the same weather data as table T in Example: One Input Matrix, Single SQL Statement and Example: One Input Matrix, Multiple SQL Statements. In this example, in addition to the point location of the observation, there is an additional VARCHAR column for continent, which is the matrix id. Our aim is to produce 7 SSCP matrixes, one for each continent’s data.

Use an SQL statement that invokes CALCMATRIX twice, once for the LOCAL phase and once for the COMBINE phase.

select * from calcmatrix( ON (
   select * from calcmatrix( ON (select continent, temperature,    air_pressure, rainfall from T) LOCAL ORDER BY continent USING    PHASE('LOCAL') )X )
   HASH BY continent
   LOCAL ORDER BY continent
   USING PHASE('COMBINE')
   )Y
   order by continent, rownum;

The result set in the following tables is the SSCP matrix for weather data by continent.

The following shows only two of the matrixes.
continent rownum rowname temperature air_pressure rainfall
Europe 1 temperature n n n
Europe 2 air_pressure n n n
Europe 3 rainfall n n n
continent rownum rowname temperature air_pressure rainfall
Asia 1 temperature n n n
Asia 2 air_pressure n n n
Asia 3 rainfall n n n