[
https://issues.apache.org/jira/browse/TRAFODION-2522?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15925470#comment-15925470
]
ASF GitHub Bot commented on TRAFODION-2522:
-------------------------------------------
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1001#discussion_r106079132
--- 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 --
Thanks for catching this, Hans. :)
1. First Question:
Yes, it was disappeard. I have asked help from William, we both don't know
why.
The first commit (and its branch) were not deleted, but the commit log is
not shown up after `git log`, but shown after `git reflog`.
Anyway, I'v added the missing parts and updated the contents based on your
comments, so
I only want to commit the changes shown now for the pull request.
2. Second Quesiton:
I've added the WITH syntax to the 3.47., my thoughts are as following,
correct?
> 3.47 SELECT Statement
> 3.47.1. Syntax Description of SELECT
> 3.47.2. Considerations for SELECT
> 3.47.3. Examples of SELECT
> 3.47.4. Syntax Description of WITH clause
> 3.47.5. Considerations for WITH clause
> 3.47.6. Examples of WITH clause
> 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)
