[
https://issues.apache.org/jira/browse/TRAFODION-2522?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15927402#comment-15927402
]
ASF GitHub Bot commented on TRAFODION-2522:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1001#discussion_r106331608
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---
@@ -7784,6 +7784,120 @@ SELECT (SELECT a FROM t1) FROM t GROUP BY (SELECT a
FROM t1);
SELECT a+1 FROM t GROUP BY 1+a;
```
+=======
+[[with_clause_syntax]]
+=== Syntax Description of WITH clause
+
+WITH clause, known as Common Table Expressions (CTEs) or subquery
factoring clause, was introduced in the SQL-99 standard and has been
implemented into Trafodion R2.1.
+
+For a complex expression which is referenced multiple times within the
body of a SELECT statement, the WITH clause assigns it an alias. The alias is
treated like a temporary table or an inline view that lasts only for the
duration of the query and can be referenced various times in the same query.
+
+By abstracting the complicated parts of the query into simpler, separate
and logical blocks, and materializing the results of these parts to avoid
recomputing it multiple times, the WITH clause has following advantages:
+
+* Simplifies complicated queries, increasing readability and reducing
repeated references.
+* Builds reusable unit and decreases maintenance cost.
+* Shortens response time and enhances performance of the query.
+* Improves compatibility with other systems which support WITH clause as
well.
+
+
+`with cte-table-name as (sql-query)`
+
+* `_cte-table-name_`
+
++
+Specifies the unique name of the CTE to be created, which is a valid SQL
identifier with a maximum of 128 characters. Duplicate names are not allowed in
a single WITH clause.
+
+* `_sql-query_`
+
++
+Specifies the query expression that will be denoted by the CTE. For more
information, see <<select_statement,SELECT Statement>> .
+
+[[with_clause_considerations]]
+=== Considerations for WITH clause
+
+* Materialization of CTEs in temporary tables is not yet enabled by
default.
+
+* Trafodion only supports non-recursive common table expressions, which
means WITH clause cannot be self-referencing, but it can reference a previously
defined CTE within the same WITH clause.
+
+* WITH clause in Trafodion can be specified only in SELECT statement.
INSERT, UPDATE, DELETE and CREATE VIEW statements are not supported yet.
+
+[[with_clause_examples]]
+=== Examples of WITH clause
+
+* The following example defines two CTEs, w1 and w2.
+w2 references w1 which is defined before w2.
+```
+>>select * from t1
+
+ C1 C2
+ ----------
+
+ 1 1
+ 2 2
+ 3 3
+ 4 4
+ 5 5
+
+ --- SQL operation complete.
+
+>>with w1 as (select * from t1),
+>>w2 as (select * from w1)
+>>select * from w2;
+
+ C1 C2
+ ----------
+
+ 1 1
+ 2 2
+ 3 3
+ 4 4
+ 5 5
+
+ --- SQL operation complete.
+```
+
+* The following example defines two CTEs, w1 and w2, and then perform a
JOIN between them.
+```
+>>select * from t1
+
+ C1 C2
+ ----------
+
+ 1 1
+ 2 2
+ 3 3
+ 4 4
+ 5 5
+
+ --- SQL operation complete.
+
+>>select * from t2
+
+ C1 C2
+ ----------
+
+ 3 3
+ 4 4
+ 5 5
+ 6 6
+ 7 7
+
+ --- SQL operation complete.
+
+>>with w1 as (select c1, c2 from t3),
--- End diff --
Hi @liuyu000, what I was wondering is whether we also need to update the
SELECT syntax at the beginning of section 3.47. We now allow a SELECT statement
to start with the WITH clause, so that should be part of the syntax.
> Add WITH Clause
> ---------------
>
> Key: TRAFODION-2522
> URL: https://issues.apache.org/jira/browse/TRAFODION-2522
> Project: Apache Trafodion
> Issue Type: Documentation
> Reporter: Liu Yu
> Assignee: Liu Yu
>
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
