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.
with tmp as (select 1 as a,2 as b from sysibm.sysdummy1)
,tmp2 as (Select * from tmp)
select * from tmp2
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 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).
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.
It's not your fault, it's Teradata's implementation of WITH which is simply not correct, see: