sqlteradatawith-clause

Does Teradata support -Multiple With Clause?

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.

Solution

  • 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.