17.05 - Example: Using a Multiple Input Table Operator with DIMENSION Input - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

In this example, the table operator closest_store finds the closest retail store when a purchase is made over a cell phone. The operator takes the following input data sets:

  • The phone_purchases table, which contains entries for purchases made over a cell phone, along with normalized spatial coordinates of the phone when the online purchase was made.
  • The stores table, which contains the location of all the retail stores and their associated normalized spatial coordinates. The stores table is a smaller fact table that is provided as DIMENSION input.
    pid x_coordinate y_coordinate
    P0
    2
    1
    P1
    1
    5
    P2
    3
    2
    P3
    0
    4
sid x_coordinate y_coordinate
SO
1
4
S1
2
3

The SELECT syntax is as follows:

     SELECT pid, sid
       FROM closest_store (
       ON phone_purchases PARTITION BY pid,
       ON stores DIMENSION) AS D;

The output looks similar to the following:

 
pid
---
 
sid
---
 
P0
 
S1
 
P1
 
S0
 
P2
 
$S1
 
P3
 
$SO

You can also make the table operator call using PARTITION BY ANY, which would keep the existing distribution of the rows on the AMPs. The SELECT syntax is as follows:

     SELECT pid, sid
       FROM closest_store (
       ON phone_purchases PARTITION BY ANY,
       ON stores DIMENSION) AS D;