Teradata SQL - WITH - Use a reference of a CTE to another CTE

visibility 2,392 access_time 2 years ago languageEnglish timeline Stats
timeline Stats
Page index 2.98

In Teradata, Common Table Expression (CTE) is supported as other databases. You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE.

For example, CTE A referencing CTE B, the SQL statements looks like the following in Teradata:

WITH CTEA AS 
(SELECT * FROM CTEB)
, CTEB AS
(SELECT * FROM TABLEB)
SEL * FROM CTEA

In other databases like SQL Server, the sequence of CTEs are reverse:

WITH CTEB AS
(SELECT * FROM TABLEB)
,CTEA AS 
(SELECT * FROM CTEB)
SEL * FROM CTEA

Prerequisites

The tables I'm using are from the following page:

Querying Teradata and SQL Server - Tutorial 0: Preparation

The database schema looks like the following:

Code snippet

WITH 
CTE_DEP as
(
	select	dep.*,CTE_AGG.EmployeeCount  
	from	TD_MS_SAMPLE_DB.Department dep
	left join CTE_AGG
		on CTE_AGG.DepartmentID = dep.DepartmentID
)
,CTE_AGG as
(
	select	DepartmentID,count(distinct EmployeeID) as EmployeeCount 
	from	TD_MS_SAMPLE_DB.Employee
	group by DepartmentID
)
select	* 
from	CTE_DEP;

The output looks like the following screenshot:


info Last modified by Administrator 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext
Teradata FastLoad with Optional Quotes
visibility 1,749
thumb_up 0
access_time 2 years ago
Teradata: Rights on a Database
visibility 56
thumb_up 0
access_time 2 years ago
Create Multiset Temporary Table in Teradata
visibility 2,057
thumb_up 0
access_time 2 years ago