__ __ __ __ _____ _ _ _____ _ _ _ | \/ | \ \ / / | __ \ (_) | | / ____| | | | | | \ / |_ __\ V / | |__) | __ ___ ____ _| |_ ___ | (___ | |__ ___| | | | |\/| | '__|> < | ___/ '__| \ \ / / _` | __/ _ \ \___ \| '_ \ / _ \ | | | | | | |_ / . \ | | | | | |\ V / (_| | || __/ ____) | | | | __/ | | |_| |_|_(_)_/ \_\ |_| |_| |_| \_/ \__,_|\__\___| |_____/|_| |_|\___V 2.1 if you need WebShell for Seo everyday contact me on Telegram Telegram Address : @jackleetFor_More_Tools:
SQL Language Extension: common table expressions
Author:
Vlad Khorsun <hvlad at users.sourceforge.net>
based on work by Paul Ruizendaal for Fyracle project
Function:
Common Table Expressions is like views, locally defined within main query.
From the engine point of view CTE is a derived table so no intermediate
materialization is performed.
Recursive CTEs allow to create recursive queries. It works as below :
engine starts execution from non-recursive members,
for each evaluated row engine starts execution of each recursive member using
current row values as parameters,
if current instance of recursive member produced no rows engine returns one
step back and get next row from previous resultset
Memory and CPU overhead of recursive CTE is much less than overhead of
recursive stored procedures. Currently recursion depth is limited by hardcoded
value of 1024
Syntax:
select : select_expr for_update_clause lock_clause
select_expr : with_clause select_expr_body order_clause rows_clause
| select_expr_body order_clause rows_clause
with_clause : WITH RECURSIVE with_list
| WITH with_list
with_list : with_item
| with_item ',' with_list
with_item : symbol_table_alias_name derived_column_list AS '(' select_expr ')'
select_expr_body : query_term
| select_expr_body UNION distinct_noise query_term
| select_expr_body UNION ALL query_term
Or, in less formal format :
WITH [RECURSIVE]
CTE_A [(a1, a2, ...)]
AS ( SELECT ... ),
CTE_B [(b1, b2, ...)]
AS ( SELECT ... ),
...
SELECT ...
FROM CTE_A, CTE_B, TAB1, TAB2 ...
WHERE ...
Rules of non-recursive common table expressions :
Several table expressions can be defined at one query
Any SELECTs clause can be used in table expressions
Table expressions can reference each other
Table expressions can be used within any part of main query or another
table expression
The same table expression can be used several times in main query
Table expressions can be used in INSERT, UPDATE and DELETE statements
(as subqueries of course)
Table expressions can be used in procedure language also
WITH statements can not be nested
References between expressions should not have loops
Example of non-recursive common table expressions :
WITH
DEPT_YEAR_BUDGET AS (
SELECT FISCAL_YEAR, DEPT_NO, SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
)
SELECT D.DEPT_NO, D.DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPARTMENT D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO AND B_1996.FISCAL_YEAR = 1996
WHERE EXISTS (SELECT * FROM PROJ_DEPT_BUDGET B WHERE D.DEPT_NO = B.DEPT_NO)
Rules of recursive common table expressions :
Recursive CTE has reference to itself
Recursive CTE is an UNION of recursive and non-recursive members
At least one non-recursive member (anchor) must be present
Non-recursive members are placed first in UNION
Recursive members are separated from anchor members and from each
other with UNION ALL clause
References between CTEs should not have loops
Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM,
COUNT, MAX etc) are not allowed in recursive members
Recursive member can have only one reference to itself and only in FROM clause
Recursive reference can not participate in outer joins
Example of recursive common table expressions :
WITH RECURSIVE
DEPT_YEAR_BUDGET AS
(
SELECT FISCAL_YEAR, DEPT_NO, SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS
(
SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT, CAST('' AS VARCHAR(255)) AS INDENT
FROM DEPARTMENT
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT, H.INDENT || ' '
FROM DEPARTMENT D JOIN DEPT_TREE H
ON D.HEAD_DEPT = H.DEPT_NO
)
SELECT D.DEPT_NO,
D.INDENT || D.DEPARTMENT AS DEPARTMENT,
B_1993.BUDGET AS B_1993,
B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995,
B_1996.BUDGET AS B_1996
FROM DEPT_TREE D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO AND B_1996.FISCAL_YEAR = 1996