CTESQLStandard

Function

Specify a table

Format

<query expression>

       [ <with clause> ] <query expression body>
       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] 

<query expression>

       [ <with clause> ] <query expression body>
       [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<with list>

   <with list element> [ { <comma> <with list element> }... ]

<with list element>

   <query name> [ <left paren> <with column list> <right paren> ]
   AS <table subquery> [ <search or cycle clause> ]

<with column list>

   <column name list>

<query expression body>

   <query term>
   | <query expression body> UNION [ ALL | DISTINCT ]
   [ <corresponding spec> ] <query term>
   | <query expression body> EXCEPT [ ALL | DISTINCT ]
   [ <corresponding spec> ] <query term>

<query term>

   <query primary>
   | <query term> INTERSECT [ ALL | DISTINCT ]
   [ <corresponding spec> ] <query primary>

<query primary>

   <simple table>
   | <left paren> <query expression body>
   [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>

<simple table>

   <query specification>
   | <table value constructor>
   | <explicit table>

<explicit table>

   TABLE <table or query name>

<corresponding spec>

   CORRESPONDING [ BY <left paren> <corresponding column list> <right paren> ]

<corresponding column list>

   <column name list>

<order by clause>

   ORDER BY <sort specification list>

<result offset clause>

   OFFSET <offset row count> { ROW | ROWS }

<fetch first clause>

   FETCH { FIRST | NEXT } [ <fetch first row count> ] { ROW | ROWS } ONLY

<offset row count>

   <simple value specification>

<fetch first row count>

   <simple value specification>

Syntax Rules

1. Let QE be the <query expression>.
2. If <with clause> is specified, then:
1. If a <with clause> WC immediately contains RECURSIVE, then WC, its <with list>, and its <with list element>s are said to be potentially recursive. Otherwise they are said to be non-recursive.
2. Let n be the number of <with list element>s. For each i , 1 (one) ≤ i < n, for each j, i < j ≤ n, the j-th <with list element> shall not immediately contain a <query name> that is equivalent to the <query name> immediately contained in the i-th <with list element>.
3. If the <with clause> is non-recursive, then for all i between 1 (one) and n, the scope of the <query name> WQN immediately contained in the i-th <with list element> WLEi is the <query expression> simply contained in every <with list element> WLEk, where k ranges from i+1 to n, and the <query expression body> immediately contained in <query expression>. A <table or query name> contained in this scope that immediately contains WQN is a query name in scope.
4. If the <with clause> is potentially recursive, then for all i between 1 (one) and n, the scope of the <query name> WQN immediately contained in the i-th <with list element> WLEi is the <query expression> simply contained in every <with list element> WLEk, where k ranges from 1 (one) to n, and the <query expression body> immediately contained in <query expression>. A <table or query name> contained in this scope that immediately contains WQN is a query name in scope.
5. For every <with list element> WLE, let WQE be the <query expression> specified by WLE and let WQT be the table defined by WQE.
1. If any two columns of WQT have equivalent names or if WLE is potentially recursive, then WLE shall specify a <with column list>. If WLE specifies a <with column list> WCL, then:
1. Equivalent <column name>s shall not be specified more than once in WCL.
2. The number of <column name>s in WCL shall be the same as the degree of WQT.
2. Every column of a character string type in WQT shall have a declared type collation.
6. A query name dependency graph QNDG of a potentially recursive <with list> WL is a directed graph
such that, for i ranging from 1 (one) to the number of <query name>s simply contained in WL:
1. Each node represents a <query name> WQNi immediately contained in a <with list element> WLEi of WL.
2. Each arc from a node WQNi to a node WQNj represents the fact that WQNj is referenced by a <query name> contained in the <query expression> simply contained in WLEi. WQNi is said to depend immediately on WQNj.
7. For a potentially recursive <with list> WL with n elements, and for i ranging from 1 (one) to n, let WLEi be the i-th <with list element> of WL, let WQNi be the <query name> immediately contained in WLEi, let WQEi be the <query expression> simply contained in WLEi, let WQTi be the table defined by WQEi, and let QNDG be the query name dependency graph of WL.
1. WL is said to be recursive if QNDG contains at
least one cycle. Case:
1. If QNDG contains an arc from WQNi to itself, then WLEi, WQNi, and WQTi are said to be recursive. WQNi is said to belong to the stratum of WQEi.
2. If QNDG contains an arc from WQNi to itself, then WLEi, WQNi, and WQTi are said to be recursive. WQNi is said to belong to the stratum of WQEi.
3. Among the WQEi, ... WQEk of a given stratum, there shall be at least one <query expression>, say WQEj, such that:
1. WQEj is a <query expression body> that immediately contains UNION.
2. WQEj has one operand that does not contain a <query name> referencing any of WQNi, ..., WQNk. This operand is said to be the non-recursive operand of WQEj.
3. WQEj is said to be an anchor expression, and WQNj an anchor name.
4. Let CCCG be the subgraph of QNDG that contains no nodes other than WQNi, ..., WQNk. For any anchor name WQNj, remove the arcs to those query names WQNl that are referenced by any <query name> contained in WQEj. The remaining graph SCCGP shall not contain a cycle.
2. If WLEi is recursive, then Case:
1. If WQEi contains at most one WQNk that belongs to the stratum of WQEi, then WLEi is linearly recursive.
2. Otherwise, let WQEi contain any two <query name>s referencing WQNk and WQNl, both of which belong to the stratum of WQEi. Case:
1. WLEi is linearly recursive if each of the following conditions is satisfied:
1. WQEi does not contain a <table reference list> that contains <query name>s referencing both WQNk and WQNl.
2. WQEi does not contain a <joined table> of which TR1 and TR2 are the first and second <table reference>s, or the <table reference> and <table factor>, respectively, and such that TR1 and TR2 contain <query name>s referencing WQNk and WQNl, respectively.
3. WQEi does not contain a <table expression> that immediately contains a <from clause> that contains WQNk, and immediately contains a <where clause> containing a <query expression> that contains a <query name> referencing WQNl.
2. Otherwise, WLEi is said to be non-linearly recursive.
3. For each WLEi, for i ranging from 1 (one) to n, and for each WQNj that belongs to the stratum of WQEi:
1. WQEi shall not contain a <query expression body> that contains a <query name> referencing WQNj and immediately contains EXCEPT where the right operand of EXCEPT contains WQNj.
2. WQEi shall not contain a <routine invocation> with an <SQL argument list> that contains one or more <SQL argument>s that immediately contain a <value expression> that contains a <query name> referencing WQNj.
3. WQEi shall not contain a <query expression> TSQ that contains a <query name> referencing WQNj, unless TSQ is simply contained in a <derived table> that is immediately contained in a <table primary> that is immediately contained in a <table reference> that is immediately contained in a <from clause> that is immediately contained in a <table expression> that is immediately contained in a <query specification> that constitutes a <simple table> that constitutes a <query primary> that constitutes a <query term> that is immediately contained in a <query expression body> that is WQEi.
4. WQEi shall not contain a <query specification> QS such that:
1. QS immediately contains a <table expression> TE that contains a <query name> referencing WQNj, and
2. QS immediately contains a <select list> SL or TE immediately contains a <having clause> HC and SL or TE contain a <set function specification>.
5. WQEi shall not contain a <query expression body> that contains a <query name> referencing WQNj and simply contains INTERSECT ALL or EXCEPT ALL.
6. WQEi shall not contain a <qualified join> QJ in which:
1. QJ immediately contains a <join type> that specifies FULL and a <table reference> or <table factor> that contains a <query name> referencing WQNj.
2. QJ immediately contains a <join type> that specifies LEFT and a <table factor> following the <join type> that contains a <query name> referencing WQNj.
3. QJ immediately contains a <join type> that specifies RIGHT and a <table reference> preceding the <join type> that contains a <query name> referencing WQNj.
7. WQEi shall not contain a <natural join> QJ in which:
1. QJ immediately contains a <join type> that specifies FULL and a <table reference> or <table primary> that contains a <query name> referencing WQNj.
2. QJ immediately contains a <join type> that specifies LEFT and a <table primary> following the <join type> that contains a <query name> referencing WQNj.
3. QJ immediately contains a <join type> that specifies RIGHT and a <table reference> preceding the <join type> that contains a <query name> referencing WQNj. iv) If WLEi is recursive, then WLEi shall be linearly recursive.
4. WLEi is said to be expandable if all of the following are true:
1. WLEi is recursive.
2. WLEi is linearly recursive.
3. WQEi is a <query expression body> that immediately contains UNION or UNION ALL. Let WQEBi be the <query expression body> immediately contained in WQEi. Let QELi and QTRi be the <query expression body> and the <query term> immediately contained in WQEBi. WQNi shall not be contained in QELi, and QTRi shall be a <query specification>.
4. WQNi is not mutually recursive.
8. If a <with list element> WLE is not expandable, then it shall not immediately contain a <search or cycle clause>.