All Forums Database
simonhx 2 posts Joined 09/14
20 Jan 2015
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
 

dnoeth 4628 posts Joined 11/04
20 Jan 2015

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.
 

Dieter

simonhx 2 posts Joined 09/14
01 Apr 2015

Hi Dieter, 
That's a really awesome solution to concatenate rows into a column, so clean and to the point.
Thanks for sharing! 
Simon

You must sign in to leave a comment.