Create a recursive view that has a "with 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 output:

╔═════╦═════════╦═════╦══════╦════════╗
║ 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 ║
╚═════╩═════════╩═════╩══════╩════════╝

Which I would ultimately like to "save" as a view. I have tried CREATE RECURSIVE VIEW and several variants, but I think I'm not understanding how to get around the WITH RECURSIVE cte statement.

For a related question to understand what's going on, see this question

The syntax is `create recursive view ....`. Here's a link to the documentation: http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/SQL_Reference/B035_1144_109A/Create_Procedure-Syntax.06.77.html#ww10851072 — Andrew, Dec 02 '16 at 21:23

score 3 · Accepted Answer · answered Dec 02 '16 at 22:34

3

Okay, that was actually harder 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

)

Don't qualify the recursive join to the view. IE, JOIN test_view, not JOIN db.test_view.

answered Dec 02 '16 at 22:34

Andrew

8,445
3
28
46

Thank you. I figured it was something simple and in the context of a much more elaborate problem, it's nice to refer back to this example. – JasonAizkalns Dec 03 '16 at 20:09
still struggling -- after view creation, I am unable to access. It says the table `vt1` does not exists. Any thoughts? – JasonAizkalns Dec 05 '16 at 15:20
1

Are you attempting to create a view that references the volatile table in your question? I'm not certain you can do that. You can use a GLOBAL TEMPORARY TABLE or a regular table. – Rob Paller Dec 10 '16 at 17:27
The view can certainly reference a volatile table, but as @RobPaller points out, it wouldn't really make any sense, because the volatile table is only available for your session. – Andrew Dec 13 '16 at 02:34
@Andrew I was concerned the table definition for the volatile table not being present in normal DBC tables would prevent view from parsing. Now I have to try it myself – seeing is believing. :) – Rob Paller Dec 13 '16 at 02:38
1

@RobPaller, as long as it's all in the same session, it works for me. I was actually a little surprised. – Andrew Dec 13 '16 at 02:44

score 0 · Answer 2 · answered Dec 06 '16 at 06:14

0

volatile tables are stored on the user's' spool space quota and should be qualified with your user name.
P.s. Why are you using volatile tables in the first place?

answered Dec 06 '16 at 06:14

David דודו Markovitz

42,900
6
64
88

Create a recursive view that has a "with recursive" statement in Teradata

2 Answers2

Linked