Create view referencing recursive view, trying to concatenate field

Database

Create view referencing recursive view, trying to concatenate field

Hi,

I've been struggling to get the following piece of code to work - is there no way to get the working select statement below (See comment ** Working **) to work within a view?

create volatile table x_table (Name varchar(50), Name_Rank integer) on commit preserve rows;

insert into x_table select 'ABC', 1;
insert into x_table select 'DEF', 2;
insert into x_table select 'GHI', 3;

select * from x_table;

replace recursive view x_recursive_view (Name, Name_Rank)
as
-- Set the first row
select Name (varchar(100)), Name_Rank
from x_table
where Name_Rank = 1
union all
-- Set the subsequent rows by recursion
select a.Name || ', ' || b.Name, a.Name_Rank
from x_table a
inner join x_recursive_view b on a.Name_Rank = b.Name_Rank + 1;

-- *** Working ***
select Name
from x_recursive_view
qualify rank() over (order by Name_Rank desc) = 1;

-- *** NOT Working ***
create recursive view x_recursive_view_y (Name)
as
(select Name
from x_recursive_view
qualify rank() over (order by Name_Rank desc) = 1);

Currently, I get the following error, "[Teradata Database] [6926] WITH [RECURSIVE] clause or recursive view is not supported within WITH [RECURSIVE] definitions, views, triggers or stored proce"...

Is there a way that I can dynamically concatenate a field in a view?

Thanks, 

Simon

2 REPLIES
Senior Apprentice

Re: Create view referencing recursive view, trying to concatenate field

Hi Simon,

as you noticed you can't use a recursive view in another view.

But depending on your TD release there's are built-in group concat function: XMLAGG

SELECT
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Name)|| ',' ORDER BY Name_Rank) (VARCHAR(1000))))
FROM x_table

This function returns a CLOB, but the maximum length for the concatenated values is still limited to 64000 bytes, so change the VARCHAR to the needed size.

Re: Create view referencing recursive view, trying to concatenate field

Hi Dieter, 

That's a really awesome solution to concatenate rows into a column, so clean and to the point.

Thanks for sharing! 

Simon