How to use dervied table in another derived table using with cluase in SQL?

Database
Enthusiast

How to use dervied table in another derived table using with cluase in SQL?

TD:
with tmp as (select 1 as a,2 as b),
tmp2 as (Select * from tmp)
select * from tmp2;

Output: select failed, object tmp doesn't exist.

Db2:

with tmp as (select 1 as a,2 as b from sysibm.sysdummy1)
,tmp2 as (Select * from tmp)
select * from tmp2

Output:
A B
1 2

Hi All,

I have to convert db2 query to TD, which has with clause(derived tables). When i run the query i am getting error.

Can anyone please help here.

Thanks,

4 REPLIES
Enthusiast

Re: How to use dervied table in another derived table using with cluase in SQL?

SEL TMP.* FROM 
(SEL 1 AS A,2 AS B) TMP
Enthusiast

Re: How to use dervied table in another derived table using with cluase in SQL?

Hi Yuva,

Thanks for your reply.

The derived table tmp has a big query with multiple joins and used in many places. if i repalce tmp table by sub query, it will be very big.

I can achieve this using volatile table. But i am just wondering is there any way to use as it is(derived tables).

Thanks,

Enthusiast

Re: How to use dervied table in another derived table using with cluase in SQL?

http://www.info.teradata.com/htmlpubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1146_112A/ch01.034.132.html#ww13804885

In TD, the scope of a derived table is limited only to the select statement.

WITH clause cannot be used in a derived table.

Link above can give you more insight.

Junior Contributor

Re: How to use dervied table in another derived table using with cluase in SQL?

It's not your fault, it's Teradata's implementation of WITH which is simply not correct, see:

Using WITH Statement Modifier instead of Temp Tables?