A recursive query may include multiple recursive queries, as long as they are disjoint. It may also include a mix of recursive and non-recursive common table expressions. The RECURSIVE keyword must be present if at least one of the common table expressions is recursive.

For example, the following query—which returns the same result as the previous query—uses a second, non-recursive common table expression to select the length of the shortest route. The definition of the second common table expression is separated from the definition of the first by a comma.

WITH RECURSIVE trip ( route, destination, previous, distance, segments ) AS ( SELECT CAST( origin || ', ' || destination AS VARCHAR(256) ), destination, origin, distance, 1 FROM travel WHERE origin = 'Kitchener' UNION ALL SELECT route || ', ' || v.destination, v.destination, v.origin, t.distance + v.distance, segments + 1 FROM trip t JOIN travel v ON t.destination = v.origin WHERE v.destination <> 'Kitchener' AND v.destination <> t.previous AND v.origin <> 'Pembroke' AND segments < ( SELECT count(*)/2 FROM travel ) ), shortest ( distance ) AS -- Additional, ( SELECT MIN(distance) -- non-recursive FROM trip -- common table WHERE destination = 'Pembroke' ) -- expression SELECT route, distance, segments FROM trip WHERE destination = 'Pembroke' AND distance < 1.5 * ( SELECT distance FROM shortest ) ORDER BY distance, segments, route; |

Like non-recursive common table expressions, recursive expressions, when used within stored procedures, may contain references to local variables or procedure parameters. For example, the best_routes procedure, defined below, identifies the shortest routes between the two named cities.

CREATE PROCEDURE best_routes ( IN initial VARCHAR(10), IN final VARCHAR(10) ) BEGIN WITH RECURSIVE trip ( route, destination, previous, distance, segments ) AS ( SELECT CAST( origin || ', ' || destination AS VARCHAR(256) ), destination, origin, distance, 1 FROM travel WHERE origin = initial UNION ALL SELECT route || ', ' || v.destination, v.destination, -- current endpoint v.origin, -- previous endpoint t.distance + v.distance, -- total distance segments + 1 -- total number of segments FROM trip t JOIN travel v ON t.destination = v.origin WHERE v.destination <> initial -- Don't return to start AND v.destination <> t.previous -- Prevent backtracking AND v.origin <> final -- Stop at the end AND segments -- TERMINATE RECURSION! < ( SELECT count(*)/2 FROM travel ) ) SELECT route, distance, segments FROM trip WHERE destination = final AND distance < 1.4 * ( SELECT MIN( distance ) FROM trip WHERE destination = final ) ORDER BY distance, segments, route; END; |

The following statement calls the previous procedure.

CALL best_routes ( 'Pembroke', 'Kitchener' ); |

Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |