SQL "WITH" to include multiple derived tables

Question

Can I write something like below. But this is not giving proper output in WinSQL/Teradata

with
a (x) as ( select 1 ),
b (y) as ( select * from a )

select * from b

Teradata supports common table expressions according to page 224 in [this doc](http://tunweb.teradata.ws/tunstudent/TeradataUserManuals/Teradata_SQL_Quick_Reference.pdf) Can you try it without using WinSQL? — Mike Sherrill 'Cat Recall', Mar 18 '14 at 15:19

score 0 · Answer 1 · answered Mar 18 '14 at 19:56

Do you really need to use CTEs for this particular solution when derived tables would work as well:

SELECT B.*
 FROM (SELECT A.* 
         FROM (SELECT 1 AS Col1) A
      ) B;

That being said, I believe multiple CTEs are available in Teradata 14.10 or 15. I believe support for a single CTE and the WITH clause were introduced in Teradata 12 or 13.

score 0 · Answer 2 · answered Oct 20 '15 at 20:21

You call the dependent 1st and then the parent like this and it will work. Why is it like that ? Teradata likes people to play with it longer and spend more time with it, making it feel important

    with

"b" (y) as ( select * from "a" ),
"a" (x) as ( select '1' )

select * from b

SQL "WITH" to include multiple derived tables

2 Answers2