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)
-- Set the first row
select Name (varchar(100)), Name_Rank
where Name_Rank = 1
-- 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 ***
qualify rank() over (order by Name_Rank desc) = 1;
-- *** NOT Working ***
create recursive view x_recursive_view_y (Name)
qualify rank() over (order by Name_Rank desc) = 1);
Currently, I get the following error, "[Teradata Database]  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?
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
TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Name)|| ',' ORDER BY Name_Rank) (VARCHAR(1000))))
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.
That's a really awesome solution to concatenate rows into a column, so clean and to the point.
Thanks for sharing!