# All Recursive View Definitions Must Be Linear - Analytics Database - Teradata Vantage

## SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category

SQL does not support non-linear recursion.

When recursion is linear, invoking a recursive view produces at most 1 direct invocation of that recursive view. To enforce linearity, SQL restricts table references to a single occurrence in the recursive view definition, which can be specified either in the FROM clause of the recursive statement or in a subquery in the recursive statement, but not in both.

A recursion is said to be linear if it obeys the following rules.

• For every joined table in the recursive view definition, the recursively-defined table is referenced only once.
• The recursively-defined table is not referenced in both the FROM clause and in a subquery of the same query specification.
• The recursively-defined table is not referenced more than once in the FROM clause of the query specification.

If any of these conditions is not met, then the recursion is non-linear.

The following create text is an example of a non-valid attempt to create a recursive view definition. The definition is not valid because the recursive view Fibonacci is referenced more than once (using the column alias names p and pp, respectively) in the FROM clause of the recursively-defined relation in the view definition. As a result, it violates the rule stated in the third bullet.

```     CREATE RECURSIVE VIEW fibonacci (n, f, mycount) AS
SELECT a, b, 0 AS mycount
FROM t
WHERE (a=0
AND    b=0)
OR    (a=1
AND    b=1)
UNION ALL
SELECT n+1, p.f + pp.f, p.mycount + 1
FROM fibonacci AS p, fibonacci AS pp
WHERE (p.n - 1) = pp.n
AND   p.mycount <= 100;```

The following similarly conceived query that uses derived tables has the same problem.

```     CREATE RECURSIVE VIEW fibonacci (n, f, mycount) AS
SELECT  n, f, mycount
FROM (SELECT 0,0,0) AS a (n,f,mycount)
UNION ALL
SELECT n, f, mycount
FROM (SELECT 1,1,0) AS b (n, f, mycount)
UNION ALL
SELECT n+1, p.f + pp.f, p.mycount + 1
FROM fibonacci AS p, fibonacci AS pp
WHERE (p.n - 1) = pp.n
AND   p.mycount <= 100;```