Defining two derived tables using WITH clause ??

Database
Enthusiast

Defining two derived tables using WITH clause ??

Following is a way to define a derived table using a WITH clause in the starting of the query and using it in the query thereafter. Is there a way to define more than one derived tables using WITH clause.

I understand the use of VT, GT and other temp tables but still need to know if there is a way to do that using WITH clause.

WITH TEMP_TAB(a,b,c)

AS

(Select

a,b,c

from table)

Thanks

20 REPLIES

Re: Defining two derived tables using WITH clause ??

>Is there a way to define more than one derived tables using WITH clause.

No, just another one stupid restriction.

Teradata Employee

Re: Defining two derived tables using WITH clause ??

Unfortunately not. We encountered this issue on a DB2 conversion. It is a much cleaner way to work with derived tables. It would be nice if Teradata supported it.

Senior Apprentice

Re: Defining two derived tables using WITH clause ??

It's supported in TD14

Dieter

Enthusiast

Re: Defining two derived tables using WITH clause ??

Thanks Dieter,

Thats a great enhancement !!

Teradata Employee

Re: Defining two derived tables using WITH clause ??

That is great news Dieter. Thanks for posting. My current client will love this information.

Enthusiast

Re: Defining two derived tables using WITH clause ??

Hi Dieter,

Tried the below syntaz but it's not working

WITH TEMP_TAB(a,b,c)

AS

(Select

a,b,c

from table)

WITH TEMP_TAB2(a,x,y)

AS

(Select

a,x,y

from table)

Sel derived_columns from TEMP_TAB inner join TEMP_TAB2

Can you please give me the link of teradata manual where i can find more details.

Thanks,

Ambuj

Senior Apprentice

Re: Defining two derived tables using WITH clause ??

Replace the 2nd WITH with a comma:

WITH TEMP_TAB(a,b,c)
AS
(Select
a,b,c
from table)
,TEMP_TAB2(a,x,y)
AS
(Select
a,x,y
from table)

Sel derived_columns from TEMP_TAB inner join TEMP_TAB2
Enthusiast

Re: Defining two derived tables using WITH clause ??

Thank you so mcuh Dieter.

Enthusiast

Re: Defining two derived tables using WITH clause ??

On a related subject, is it possible to insert this data into a table?

I wrote a pretty complex query recently using the 'with' syntax, but when I tried to use it as an insert/select query I couldn't get it to work, and had to create volatile tables instead.  Should it be possible to do so?