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

access_time 7 months ago visibility391 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 2 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 2654
thumb_up 1
access_time 7 months ago

In ANSI SQL, you can use DATEADD function to easily add or subtract days/week/months/years from a date as the following code snippet shows: SELECT DATEADD ( month , 1 , '20060830' ); SELECT DATEADD ( day , - 1 , '20060831' ); However in Teradata this function is not implemented and you ...

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 13
thumb_up 0
access_time 30 days ago

This page shows how to create partitioned Hive tables via Hive SQL (HQL). Example: CREATE TABLE IF NOT EXISTS hql.transactions(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2),txn_type STRING, created_date DATE) COMMENT 'A table to store transactions' PARTITIONED BY (txn_date DATE) STORED AS ...

local_offer teradata local_offer teradata-tool

visibility 16028
thumb_up 0
access_time 7 years ago

In this article, I am going to introduce how to install Teradata Express in virtual machines in Windows. 1) Download VMware Player for Windows 32-bit and 64-bit from the following link (version 6.0): https://my.vmware.com/web/vmware/free#desktop_end_user_computing/vmware_player/6_0 This is ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS