Example: LOCAL ORDER BY Option - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.