Common Table Expressions (CTE) in Teradata

  1. Forget Code
  2. Teradata
  3. Common Table Expressions (CTE)
Common table expressions(CTE) can be used in Teradata for recursive queries.

Syntax:

WITH CTE_name(column list) AS
(
SELECT column list FROM table [(WHERE condition)]
)
SELECT * FROM CTE_name;


Note:
The column list must match between CTE and SELECT statement.
Multiple WITH clauses are not allowed in Teradata.

Example:
Consider the following table tbl_emp
Emp_idEmp_namecity
03RiyaNew Delhi
11RoselinBerlin
13RajeshNew Delhi
12RosaryMoscow

WITH cte_emp(city) AS
(
    SELECT city FROM tbl_emp WHERE Emp_id=03
    UNION ALL
    SELECT city FROM tbl_emp WHERE Emp_id=12
)
SELECT * FROM cte_emp;

Output:
city
New Delhi
Moscow