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

access_time 9 months ago visibility601 comment 0

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 at 4 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 55
thumb_up 0
access_time 3 months ago

Teradata RPAD function is used to add repeated characters at the end of a string to increase the string to a specified length. It can be used to add trailing space or zeros to a string. RPAD(source_string, length, fill_string) Returns the source_string padded to the right with the characters in ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 153
thumb_up 0
access_time 3 months ago

Teradata LPAD function is used to add repeated characters at the beginning of a string to increase the string to a specified length. It can be used to add leading space or zeros to a string. LPAD(source_string, length, fill_string) Returns the source_string padded to the left with the ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 153
thumb_up 0
access_time 3 months ago

Teradata LPAD function is used to add repeated characters at the beginning of a string to increase the string to a specified length. It can be used to add leading space or zeros to a string. LPAD(source_string, length, fill_string) Returns the source_string padded to the left with the ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS