17.05 - Example: Using the LOCAL ORDER BY Option - 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)

This example uses the same tables as the previous example except that it uses a new version of orders_staging that has an integer column named o_sequence, which it uses to order the rows it selects locally.

     ALTER TABLE orders_staging
     ADD o_sequence INTEGER;

The following INSERT …  SELECT request locally orders the rows of the columns it selects from orders_staging by o_sequence before inserting them into orders.

     INSERT INTO orders
       SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice+10,
              o_ordertsz, o_comment
       FROM orders_staging 
       LOCAL ORDER BY o_sequence;

For the LOCAL ORDER BY clause, o_sequence does not resolve to a column in the target table orders, so the database resolves using the standard rules for resolution to o_sequence in the underlying table of the SELECT request, orders_staging. Note that orders_staging.o_sequence is not included in the select expression list and the spool is generated locally and sorted on the value of orders_staging.o_sequence before being locally inserting into orders.