16.20 - Examples - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

Example: Using a WITH Modifier in CREATE VIEW

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.

Following is the table definition for this example.

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);
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 uses a named query in the WITH modifier to create the view sales_v. The 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.

CREATE VIEW sales_v AS
   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;

Then, you can query the view.

SELECT * FROM sales_v
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.

CREATE VIEW sales_v AS
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;

Then, you can query the view.

SELECT * FROM sales_v
ORDER BY total_spend DESC;

The query returns this answer set:

customer_id total_spend
43563 2.25270000000000E 003
23235 1.96421000000000E 003

Example: Using a Recursive Query in a WITH Modifier in CREATE VIEW

This shows the view t1_view based on the recursive query s5.

CREATE VIEW t1_view AS WITH RECURSIVE  s5 (MinVersion_view) AS (SELECT a1 FROM t1 WHERE a1 > 1
UNION ALL
SEL MinVersion_view FROM s5 WHERE MinVersion_view > 3),
RECURSIVE s6 (MinVersion_view2) AS (SELECT a1 FROM t1 WHERE a1 = 2
UNION ALL
SEL MinVersion_view2 FROM S6 WHERE MinVersion_view2 > 2)
SEL * FROM s5,s6;
This statement displays the content of the view t1_view.
SEL * FROM t1_view;
MinVersion_view MinVersion_view2
3 2
2 2