All Forums Database
NewbieTD 25 posts Joined 05/13
30 Jun 2015
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,

yuvaevergreen 93 posts Joined 07/09
30 Jun 2015
SEL TMP.* FROM 
(SEL 1 AS A,2 AS B) TMP

 

NewbieTD 25 posts Joined 05/13
30 Jun 2015

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,

yuvaevergreen 93 posts Joined 07/09
30 Jun 2015
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.
 
 
 
 

dnoeth 4628 posts Joined 11/04
30 Jun 2015

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?
 

Dieter

You must sign in to leave a comment.