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

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

SO

1

4

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;