Does Teradata support -Multiple With Clause?

Question

Trying to execute in SQLAssitant (v 15.x Teradata):

WITH   TEMP1 (EMP_ID,E_NAME,E_SAL) AS (WITH TEMP (EMP_ID,E_NAME,E_SAL) AS (SELECT EMP_ID,E_NAME,E_SAL FROM EMP_TABLE_TEST)
SELECT EMP_ID,E_NAME,E_SAL FROM TEMP) SELECT EMP_ID,E_NAME,E_SAL FROM TEMP1

Error: SELECT Failed. 6926: definitions, views, triggers or stored procedure

WITH TEMP (EMP_ID,E_NAME,E_SAL) AS (SELECT EMP_ID,E_NAME,E_SAL FROM EMP_TABLE_TEST ) , TEMP1  (EMP_ID,E_NAME,E_SAL) AS (
SELECT EMP_ID,E_NAME,E_SAL FROM TEMP) SELECT  EMP_ID,E_NAME,E_SAL FROM TEMP1

Error: SELECT Failed. 3807: Object 'TEMP' does not exist.

Does Teradata really support Multiple WITH clause or WITH within WITH clause?

I heard it is supported in 14.x higher version but it is not supporting for 15.x.

http://community.teradata.com/t5/Database/Defining-two-derived-tables-using-WITH-clause/td-p/46164 — Santiago Elvira Ramirez, Nov 16 '16 at 08:43
I was expecting 15.x will support with query.. but it is no.. — Ganesh Kumar, Nov 16 '16 at 10:27

David דודו Markovitz · Accepted Answer · 2016-11-16T08:05:26.167

3

The syntax is different (and is the same as in other databases)
With t1 as (...),t2 as (...), t3 as (...) select ...

Currently the reference order is upside-down -
t2 can refer t3 and t1 can refer t2 and t3.
The "right" order will be supported in TD16.

edited Nov 16 '16 at 08:05

answered Nov 16 '16 at 07:55

David דודו Markovitz

42,900
6
64
88

Can i ? With t1 as (Select..from ABC.),t2 as (Select...from t1), t3 as (Select.. from t2.) select ...from t3. my requirement is like this..? – Ganesh Kumar Nov 16 '16 at 10:29
1

As I wrote, you should change the order: `with t3 as ...,t2 as...,t1 as... Select` – David דודו Markovitz Nov 16 '16 at 10:37
Thanks Dudu Markovitz, your answer worked for me.. it would be better if teradata would have supported in order like Netezza, PostGre....etc.. – Ganesh Kumar Nov 16 '16 at 13:30

score 0 · Answer 2 · edited Feb 15 '17 at 10:18

This has been fixed in Teradata 16. Please see the release summary chapter 2.

http://www.info.teradata.com/doclist.cfm?RetainParams=Y&FilterCall=Y&selDocType=100

Previously, when a nonrecursive WITH clause defined multiple CTEs, a CTE could only reference a subsequent CTE in the WITH clause. Now, a CTE can reference a preceding or subsequent CTE in the WITH clause.

From Teradata Release Summary for version 16

Does Teradata support -Multiple With Clause?

2 Answers2