WITH Examples | SQL SELECT Statements | Teradata Vantage - Example: Common Table Expression - 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™

A common table expression (CTE) in a WITH modifier can reference either preceding or subsequent CTEs defined in the WITH modifier, provided that the CTE does not indirectly reference itself. That is, circular references are not allowed. CTEs are also referred to as named queries.

Following is the table definition for this example.

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);

These statements insert rows of data into the table.

INSERT INTO orders (43563, 734.12);
INSERT INTO orders (65758, 211.15);
INSERT INTO orders (23235, 1264.98);
INSERT INTO orders (43563, 583.23);
INSERT INTO orders (89786, 278.66);
INSERT INTO orders (13253, 401.97);
INSERT INTO orders (98765, 1042.23);
INSERT INTO orders (23235, 699.23);
INSERT INTO orders (43563, 935.35);
INSERT INTO orders (88354, 375.09);

This example of a WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is previously defined in the WITH clause.

WITH multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
),
multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set:

customer_id total_spend
43563 2.25270000000000E 003
23235 1.96421000000000E 003

This example of a WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is subsequently defined in the WITH clause.

WITH multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
),
multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set:

customer_id total_spend
43563 2.25270000000000E 003
23235 1.96421000000000E 003