loading..
Русский    English
15:43

Recursive CTE page 1

A  CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE has one more important. With it, you can write a recursive query, i.e. request, which, once written, will be repeated many times until some condition is true. A recursive CTE is as follows:

  1. WITH < name >[(< COLUMN list >)]
  2. AS(
  3. < SELECT... > -- Anchor part.
  4. UNION ALL -- Recursive part.
  5. < SELECT...FROM < name >… >
  6. WHERE < condition FOR the continuation of iterations >
  7. )

A recursive CTE-query differs from ordinary CTE in recursive part only that introduced by UNION ALL statement. Note that a there is reference to CTE name in recursive part, i.e., CTE refers to itself within itself. This is, in fact, there is recursion. Naturally, anchor and recursive parts must have identical column set.

We turn to the example. Consider the task of getting the alphabet, i.e. table of alphabetic characters - uppercase Latin letters. To be a base for comparison, we solve this problem, first by generating a numerical sequence, which was seen in section 8.1.

Console
Execute
  1. SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS num
  2. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
  3.  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  4.  ) x CROSS JOIN
  5.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
  6.  UNION ALL SELECT 4 UNION ALL SELECT 5
  7.  ) y
  8. WHERE 5*(a-1) + b <= 26
  9. ORDER BY 1;

The decision via the recursive CTE:

Console
Execute
  1. WITH Letters AS(
  2. SELECT ASCII('A') code, CHAR(ASCII('A')) letter
  3. UNION ALL
  4. SELECT code+1, CHAR(code+1) FROM Letters
  5. WHERE code+1 <= ASCII('Z')
  6. )
  7. SELECT letter FROM Letters;

In anchor part of query we define ASCII-code of the first letters of the alphabet and the corresponding symbol. In recursive part of query we simply increase the ASCII-code by one, referring to the CTE in the FROM clause. As a result, the lines with next letters will be added sequentially (UNION ALL) after line with first letter, in the order of their ASCII-code. Iteration will continue as long as the condition code +1 <= ascii ('Z') is true, i.e. until a letter Z will not be added.

The statement

Console
Execute
  1. SELECT letter FROM Letters
actually intended to refer to the CTE, start recursion and output results. Everything else can be regarded as a definition. Note that by default is allowed 100 iterations. This means that if the termination condition is not fulfilled earlier iterations, then the recursion is stopped after 100 iterations. Maximum number of iterations can be changed using "hint"

  1. OPTION(MAXRECURSION N)
where N - the maximum number of iterations. A value of 0 does not limit the number of iterations. We must carefully use this value as is fraught with cycling. If the query was not completed within a specified number of iterations, an error (the rows received to this time are returned):

The statement terminated. The maximum recursion N has been exhausted before statement completion.


Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100