[
https://issues.apache.org/jira/browse/TRAFODION-2522?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15903377#comment-15903377
]
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_r105213808
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---
@@ -7784,6 +7784,117 @@ 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 the complex expression which is referenced multiple times within the
body of SELECT Statement, WITH clause assigns it an alias which 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 result set to prevent from recomputing
multiple times as the data can be easily acquired from the disk-based
intermediate materialized table rather than requeried with each reference, WITH
clause has following advantages:
+
+* Simplify complicated query, increase readability and reduce repeated
references.
+* Build reusable unit and decrease maintenance cost.
+* Shorten the response time and enhance the performance of the query.
+* Improve the 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_`
+
++
+This option serves the same purposes as they in SELECT Statement. For more
information, see <<select_statement,SELECT Statement>> .
+
+[[with_clause_considerations]]
+=== Considerations for WITH clause
+
+* Trafodion only supports non-recursive common table expressions, which
means WITH clause cannot be self-referencing, but it can reference the
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.
+
--- End diff --
You mention above that the WITH clause can improve performance. This is not
yet enabled by default. We could add something like the following consideration
here: Materialization of CTEs in temporary tables is not yet enabled by
default. See also TRAFODION-2127
(https://issues.apache.org/jira/browse/TRAFODION-2127).
> 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)
