Multiple with clause

Database
Enthusiast

Multiple with clause

Hi Everyone,

I am new to teradata and I was trying to convert query which is working in MS SQL to Teradata. Version of Teradata is 14.x, hence I think with clause should work.

But I am facing problem in referencing one CTE into another CTE in Teradata. I am not sure If I am doing any syntax mistake. Please help me on this.

MS SQL query:

WITH CTE1

     AS (SELECT 'aaa' AS c1,

                '01'   AS c2

         UNION

         SELECT 'bbb',

                '02'

         UNION

         SELECT 'ccc',

                '03'

         UNION

         SELECT 'ddd',

    '04'       ),

CTE2 

   AS (

           select CTE1.c1 from table_name)

Select * from CTE2

this gives me expected result. But in teradata,  select CTE1.c1 from table_name is not working and giving error. Do I need to reference in some other way or this is not possible in teradata?

9 REPLIES
Junior Contributor

Re: Multiple with clause

Well, multiple WITH are not implemented correctly in Teradata, you must define them in the wrong order:

WITH
CTE2
AS (
select CTE1.c1 from table_name)
, CTE1
AS (SELECT 'aaa' AS c1,
'01' AS c2
UNION
SELECT 'bbb',
'02'
UNION
SELECT 'ccc',
'03'
UNION
SELECT 'ddd',
'04' )

Select * from CTE2

It's totally stupid, you may open an incident to get it fixed...

Enthusiast

Re: Multiple with clause

Hi doneth,

Thanks a lot for your reply.

I tried this approach but I am getting error that " Object CTE1 doesn't exist" when I select * from CTE2. For Select * from CTE1, I am getting Table CTE1 as per expectation.

Regards,

Vivek

Junior Contributor

Re: Multiple with clause

Hi Vivek,

can you show your actual SQL?

Enthusiast

Re: Multiple with clause

WITH

dataset 

   AS (

           select contractmap.c1 from Table)

, contractmap  as 

(SELECT 'VAST' AS c1,

                '01'   AS c2

  from Table

  union

 SELECT 'int' AS c1,

                '09'   AS c2

  from Table

  union

 SELECT 'JOB' AS c1,

                '11'   AS c2

  from Table)

  select * from dataset   

This is query I am trying to execute,

Enthusiast

Re: Multiple with clause

Hi Dieter,

I am still struggling with calling one CTE into other in teradata. Please let me know if this is possible in teradata or not. See below query which I am trying to execute:

WITH

 MainCTE 

   AS (

           select innerCTE.c1 from table ),

innerCTE as ( 

select 'vast' as c1, 'fix' as c2 from Table union

select 'int'as c1 , 'int' as c2  from Table union

select 'temp'as c1 , 'temp' as c2  from Table union 

select 'unknown' as c1 , 'fix' as c2  from Table

                   )

 Select * from MainCTE

Junior Contributor

Re: Multiple with clause

WITH MainCTE AS
(
SELECT c1 FROM innerCTE
),
innerCTE AS
(
SELECT 'vast' AS c1, 'fix' AS c2 FROM TABLE UNION

SELECT 'int'AS c1 , 'int' AS c2 FROM TABLE UNION

SELECT 'temp'AS c1 , 'temp' AS c2 FROM TABLE UNION

SELECT 'unknown' AS c1 , 'fix' AS c2 FROM TABLE
)
SELECT * FROM MainCTE
Enthusiast

Re: Multiple with clause

Hi Dieter,

Thank you very much :) This worked. Really appreciate your help.

There is small issue which I am currently facing is that as our first row is 'vast' and 'fix' , the length of column C1 and C2 gets restricted according to length of 'vast' and 'fix' respectively. Means when I do select * from main CTE, 'unknown' becomes 'unkn' and 'temp' in C2 becomes 'tem'. I have appended temporarily first row as dummy which would be having length longest so that other values don't get truncated. Is this way to go or we could overcome this by any alternative?

Regards,

Vivek

Junior Contributor

Re: Multiple with clause

Hi Vivek,

in Teradata the first Select in a set operation determines the resulting data type, simply CAST to a larger size: 

SELECT CAST('vast' AS VARCHAR(20)) AS c1, CAST('fix' AS VARCHAR(20)) AS c2 
Enthusiast

Re: Multiple with clause

Thank you Dieter,

You are great :):) All works fine now.