Create a recursive view that has a “recursive” statement in Teradata

Question

I would like to create a recursive view in Teradata (i.e. CREATE RECURSIVE VIEW ) from the following reproducible example:

 CREATE VOLATILE TABLE vt1 ( foo VARCHAR(10) , counter INTEGER , bar INTEGER ) ON COMMIT PRESERVE ROWS; INSERT INTO vt1 VALUES ('a', 1, '1'); INSERT INTO vt1 VALUES ('a', 2, '2'); INSERT INTO vt1 VALUES ('a', 3, '2'); INSERT INTO vt1 VALUES ('a', 4, '4'); INSERT INTO vt1 VALUES ('a', 5, '1'); INSERT INTO vt1 VALUES ('b', 1, '3'); INSERT INTO vt1 VALUES ('b', 2, '1'); INSERT INTO vt1 VALUES ('b', 3, '1'); INSERT INTO vt1 VALUES ('b', 4, '2'); WITH RECURSIVE cte (foo, counter, bar, rsum) AS ( SELECT foo , counter , bar , bar AS rsum FROM vt1 QUALIFY ROW_NUMBER() OVER (PARTITION BY foo ORDER BY counter) = 1 UNION ALL SELECT t.foo , t.counter , t.bar , CASE WHEN cte.rsum < 3 THEN t.bar + cte.rsum ELSE t.bar END FROM vt1 t JOIN cte ON t.foo = cte.foo AND t.counter = cte.counter + 1 ) SELECT cte.* , CASE WHEN rsum < 5 THEN 0 ELSE 1 END AS tester FROM cte ORDER BY foo , counter ; 

This creates this conclusion:

 ╔═════╦═════════╦═════╦══════╦════════╗ ║ foo ║ counter ║ bar ║ rsum ║ tester ║ ╠═════╬═════════╬═════╬══════╬════════╣ ║ a ║ 1 ║ 1 ║ 1 ║ 0 ║ ║ a ║ 2 ║ 2 ║ 3 ║ 0 ║ ║ a ║ 3 ║ 2 ║ 5 ║ 1 ║ ║ a ║ 4 ║ 4 ║ 4 ║ 0 ║ ║ a ║ 5 ║ 1 ║ 5 ║ 1 ║ ║ b ║ 1 ║ 3 ║ 3 ║ 0 ║ ║ b ║ 2 ║ 1 ║ 4 ║ 0 ║ ║ b ║ 3 ║ 1 ║ 5 ║ 1 ║ ║ b ║ 4 ║ 2 ║ 2 ║ 0 ║ ╚═════╩═════════╩═════╩══════╩════════╝ 

What I ultimately would like to “save” as a view. I tried CREATE RECURSIVE VIEW and several options, but I think I don’t understand how to get around the WITH RECURSIVE cte .

For the question of what is happening, see this question.

Answer 1

Well, it was actually more complicated than I thought:

 create recursive view db.test_view ( foo, counter,bar,rsum) as (SELECT foo, counter, bar, bar AS rsum FROM vt1 QUALIFY ROW_NUMBER() OVER (PARTITION BY foo ORDER BY counter) = 1 UNION ALL SELECT t.foo, t.counter, t.bar, CASE WHEN cte.rsum < 5 THEN t.bar + cte.rsum ELSE t.bar END FROM vt1 t JOIN test_view cte ON t.foo = cte.foo AND t.counter = cte.counter + 1 ) 

Do not qualify a recursive join for presentation. IE, JOIN test_view , not JOIN db.test_view .

Answer 2

Flying tables are stored in the user spool space quota and must be qualified with your username.
PS Why do you use volatile tables in the first place?