You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2017/03/20 20:39:32 UTC
[1/6] incubator-trafodion git commit: incorporate comments for WITH
clause
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 39de98789 -> a010b2ec9
incorporate comments for WITH clause
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/f8f48cf3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/f8f48cf3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/f8f48cf3
Branch: refs/heads/master
Commit: f8f48cf33974c70103362739bcd8de19d8c5f3dd
Parents: 03fd040
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Mar 14 16:20:53 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Mar 14 16:38:13 2017 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_statements.adoc | 114 +++++++++++++++++++
1 file changed, 114 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/f8f48cf3/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 6ea3fde..1c53aa4 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/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),
+>>w2 as (select c1, c2 from t2)
+>>select * from w1, w2 where w1.c1 = w2.c1;
+
+ C1 C2 C1 C2
+ ----------
+
+ 3 3 3 3
+ 4 4 4 4
+ 5 5 5 5
+
+ --- SQL operation complete.
+```
+
<<<
[[set_schema_statement]]
== SET SCHEMA Statement
[5/6] incubator-trafodion git commit: Merge [TRAFODION-2522] PR 1001
Add WITH clause to SQL reference guide
Posted by db...@apache.org.
Merge [TRAFODION-2522] PR 1001 Add WITH clause to SQL reference guide
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5d21a1ec
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5d21a1ec
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5d21a1ec
Branch: refs/heads/master
Commit: 5d21a1ec9e6122cc5b3da14401e72fe782fd3247
Parents: 39de987 869954c
Author: Dave Birdsall <db...@apache.org>
Authored: Mon Mar 20 20:36:08 2017 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Mon Mar 20 20:36:08 2017 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 129 +++++++++----------
.../_chapters/sql_language_elements.adoc | 6 +-
.../src/asciidoc/_chapters/sql_statements.adoc | 114 +++++++++++++++-
3 files changed, 180 insertions(+), 69 deletions(-)
----------------------------------------------------------------------
[3/6] incubator-trafodion git commit: incorporate comments for clause
2
Posted by db...@apache.org.
incorporate comments for clause 2
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/76baf828
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/76baf828
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/76baf828
Branch: refs/heads/master
Commit: 76baf8284317e7f7fb5111ac5b7590a90c381081
Parents: ed3db26
Author: liu.yu <yu...@esgyn.cn>
Authored: Sat Mar 18 14:17:02 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Sat Mar 18 14:17:02 2017 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_statements.adoc | 226 +++++++++----------
1 file changed, 112 insertions(+), 114 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/76baf828/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 1c53aa4..80ed9e2 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -6642,6 +6642,7 @@ sql-query is:
| query-expr-and-order
query-specification is:
+with-clause
SELECT [ "[" ANY N "]" | "[" FIRST N "]" ] [ALL | DISTINCT] select-list
FROM table-ref [,table-ref]...
[WHERE search-condition]
@@ -6655,6 +6656,15 @@ SELECT [ "[" ANY N "]" | "[" FIRST N "]" ] [ALL | DISTINCT] select-list
[access-clause ]
[mode-clause]
+With-clause is:
+with_clause_elements
+
+with_clause_elements is:
+with_clause_element [,with_clause_element] \u2026
+
+with_clause_element is:
+cte-table-name AS (sql-query)
+
query-expr-and-order is:
query-expr [order-by-clause] [access-clause] [mode-clause]
@@ -6758,6 +6768,26 @@ transpose-item-list is:
[[select_syntax]]
=== Syntax Description of SELECT
+* 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.
+
++
+The with-clause assigns an alias to a complex expression. 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 multiple times in the same query.
+
++
+By abstracting the complicated parts of the query into simpler, separate and logical blocks, and possibly 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 units and decreases maintenance cost.
+** Shortens response time and enhances performance of the query.
+** Improves compatibility with other systems which support with-clause as well.
+
+* 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.
+
* `"[" ANY _N_ "]" | "[" FIRST _N_ "]"`
+
specifies that _N_ rows are to be returned (assuming the table has at least _N_ rows and that the qualification
@@ -7303,6 +7333,15 @@ SELECT sum(distinct a), count(distinct a), avg(distinct a) from T group by d;
SELECT sum(distinct a), avg(distinct b), sum(c) from T group by d;
```
+[[select_considerations_for_with-clause]]
+==== 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.
+
+* The with-clause in Trafodion can be specified only once, at the beginning of a SELECT statement. INSERT, UPDATE, DELETE and CREATE VIEW statements are not supported yet.
+
[[select_considerations_for_select_list]]
==== Considerations for Select List
@@ -7436,6 +7475,79 @@ A parenthesized union of SELECT statements is evaluated first, from left to righ
[[select_examples]]
=== Examples of SELECT
+* 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 t1),
+>>w2 as (select c1, c2 from t2)
+>>select * from w1, w2 where w1.c1 = w2.c1;
+
+ C1 C2 C1 C2
+ ----------
+
+ 3 3 3 3
+ 4 4 4 4
+ 5 5 5 5
+
+ --- SQL operation complete.
+```
+
* Retrieve information from the EMPLOYEE table for employees with a job code greater than 500 and who are in departments
with numbers less than or equal to 3000, displaying the results in ascending order by job code:
+
@@ -7784,120 +7896,6 @@ 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),
->>w2 as (select c1, c2 from t2)
->>select * from w1, w2 where w1.c1 = w2.c1;
-
- C1 C2 C1 C2
- ----------
-
- 3 3 3 3
- 4 4 4 4
- 5 5 5 5
-
- --- SQL operation complete.
-```
-
<<<
[[set_schema_statement]]
== SET SCHEMA Statement
[2/6] incubator-trafodion git commit: fix identity
Posted by db...@apache.org.
fix identity
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/ed3db26d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/ed3db26d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/ed3db26d
Branch: refs/heads/master
Commit: ed3db26ddcf89e908dfc9f38ea5f753069294967
Parents: f8f48cf
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Mar 14 17:13:34 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Mar 14 17:13:34 2017 +0800
----------------------------------------------------------------------
.../src/asciidoc/_chapters/sql_language_elements.adoc | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ed3db26d/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
index b7fe71e..f260e3d 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc
@@ -1874,7 +1874,7 @@ delimited identifier.
<<<
[[identity_column]]
-== Indentity Column
+== Identity Column
A identity column is an auto-increment column, which is defined to a column of a table with identity attribute and used to automatically generate increasing or decreasing sequential numeric value for a column with each row insertion into the table.
@@ -1892,7 +1892,7 @@ GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY
```
[[syntax_description_of_identity_column]]
-=== Syntax Description of Indentity Column
+=== Syntax Description of Identity Column
* `ALWAYS`
+
@@ -1905,7 +1905,7 @@ Indicates that when a row is inserted to a table, if the value of the column is
The options above serve the same purposes as they serve when you create a sequence. For more information, see <<create_sequence_statement,CREATE SEQUENCE Statement>>.
[[examples_of_identity_column]]
-=== Examples of Indentity Column
+=== Examples of Identity Column
* Example of `ALWAYS`
+
[4/6] incubator-trafodion git commit: incorporate comments for WITH
clause 3
Posted by db...@apache.org.
incorporate comments for WITH clause 3
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/869954c3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/869954c3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/869954c3
Branch: refs/heads/master
Commit: 869954c35d0bac742d1045e9d09dfe62ab03923c
Parents: 76baf82
Author: liu.yu <yu...@esgyn.cn>
Authored: Mon Mar 20 11:41:46 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Mon Mar 20 11:53:01 2017 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 129 +++++++++----------
.../src/asciidoc/_chapters/sql_statements.adoc | 4 +-
2 files changed, 66 insertions(+), 67 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/869954c3/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index ef05776..e4e37ee 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -53,7 +53,7 @@ of the expression argument of the function.
*) or the number of rows that contain a distinct value in the one-column
table derived from the expression argument of the function (optionally
distinct values).
-| <<group_concat_function,GROUP_CONCAT Function>> | This function returns a string result with the concatenated non-NULL
+| <<group_concat_function,GROUP_CONCAT Function>> | This function returns a string result with the concatenated non-NULL
values from a group.
| <<max_function,MAX/MAXIMUM Function>> | Determines a maximum value from the group of values derived from the
evaluation of the expression argument.
@@ -64,7 +64,7 @@ derived from the evaluation of the expression argument of the function.
The numbers can be weighted.
| <<sum_function,SUM Function>> | Computes the sum of a group of numbers derived from the evaluation of
the expression argument of the function.
-"VARIANCE Function"
+"VARIANCE Function"
Computes the statistical variance of a group of numbers derived from the
evaluation of the expression argument of the function. The numbers can
be weighted.
@@ -152,11 +152,11 @@ return a result of datetime data type:
[cols="25%,75%"]
|===
-| <<add_months_function,ADD_MONTHS Function>> | Adds the integer number of months specified by _intr_expr_
+| <<add_months_function,ADD_MONTHS Function>> | Adds the integer number of months specified by _intr_expr_
to _datetime_expr_ and normalizes the result.
| <<converttimestamp_function,CONVERTTIMESTAMP Function>> | Converts a Julian timestamp to a TIMESTAMP value.
| <<current_function,CURRENT Function>> | Returns the current timestamp. You can also use the
-<<current_timestamp_function,CURRENT_TIMESTAMP Function>>.
+<<current_timestamp_function,CURRENT_TIMESTAMP Function>>.
| <<current_date_function,CURRENT_DATE Function>> | Returns the current date.
| <<current_time_function,CURRENT_TIME Function>> | Returns the current time.
| <<current_timestamp_function,CURRENT_TIMESTAMP Function>> | Returns the current timestamp. You can also use the <<current_function,CURRENT Function>>.
@@ -221,7 +221,7 @@ Use these mathematical functions within an SQL numeric value expression:
[cols="25%,75%"]
|===
-| <<abs_function,ABS Function>> | Returns the absolute value of a numeric value expression.
+| <<abs_function,ABS Function>> | Returns the absolute value of a numeric value expression.
| <<acos_function,ACOS Function>> | Returns the arccosine of a numeric value expression as an angle expressed in radians.
| <<asin_function,ASIN Function>> | Returns the arcsine of a numeric value expression as an angle expressed in radians.
| <<atan_function,ATAN Function>> | Returns the arctangent of a numeric value expression as an angle expressed in radians.
@@ -256,7 +256,7 @@ See the individual entry for the function.
[[encryption_functions]]
== Encryption Functions
Use these functions within an SQL value expression to do data encryption or hashing:
-
+
[cols="25%,75%"]
|===
| <<md5_function,MD5 Function>> | Returns MD5 checksum
@@ -352,7 +352,7 @@ Use these other functions and expressions in an SQL value expression:
| <<bitand_function,BITAND Function>> | Performs 'and' operation on corresponding bits of the two operands.
| <<case_expression,CASE (Conditional) Expression>> | A conditional expression. The two forms of the CASE expression are simple and searched.
| <<cast_expression,CAST Expression>> | Converts a value from one data type to another data type that you specify.
-| <<coalesce_function,COALESCE Function>> | Returns the value of the first expression in the list that does not have a NULL value or if all
+| <<coalesce_function,COALESCE Function>> | Returns the value of the first expression in the list that does not have a NULL value or if all
the expressions have NULL values, the function returns a NULL value.
| <<converttohex_function,CONVERTTOHEX Function>> | Converts the specified value expression to hexadecimal for display purposes.
| <<current_user_function,CURRENT_USER Function>> | Returns the database user name of the current user who invoked the function.
@@ -365,7 +365,7 @@ the expressions have NULL values, the function returns a NULL value.
| <<inet_ntoa_function, INET_NTOA Function>> | Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a nonbinary string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.
| <<nullif_function,NULLIF Function>> | Returns the value of the first operand if the two operands are not equal, otherwise it returns NULL.
| <<nvl_function,NVL Function>> | Returns the value of the first operand unless it is NULL, in which case it returns the value of the second operand.
-| <<user_function,USER Function>> | Returns either the database user name of the current user who invoked the function or the database user name
+| <<user_function,USER Function>> | Returns either the database user name of the current user who invoked the function or the database user name
associated with the specified user ID number.
|===
@@ -406,7 +406,7 @@ ABS (-20 + 12)
The ACOS function returns the arccosine of a numeric value expression as
an angle expressed in radians.
-ACOS is a {project-name} SQL extension.
+ACOS is a {project-name} SQL extension.
```
ACOS (numeric-expression)
@@ -415,7 +415,7 @@ ACOS (numeric-expression)
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
-argument of the ACOS function. The range for the value of the argument is
+argument of the ACOS function. The range for the value of the argument is
from -1 to +1. See <<numeric_value_expressions,Numeric Value_Expressions>>.
[[examples_of_acos]]
@@ -509,7 +509,7 @@ the ISO8891 character set or the UTF8 character set.
ASCII is a {project-name} SQL extension.
```
-ASCII (character-expression)
+ASCII (character-expression)
```
* `_character-expression`
@@ -1136,7 +1136,7 @@ RAYMOND JANE 3000 136000.0000
The CAST expression converts data to the data type you specify.
```
-CAST ({expression | NULL} AS data-type)
+CAST ({expression | NULL} AS data-type)
```
* `_expression_ | NULL`
@@ -1870,38 +1870,38 @@ SELECT COUNT (DISTINCT deptnum) FROM persnl.employee;
<<<
[[crc32_function]]
== CRC32 Function
-
-Computes a cyclic redundancy check value and returns a 32-bit unsigned value.
-The result is NULL if the argument is NULL. The argument is expected to be a
+
+Computes a cyclic redundancy check value and returns a 32-bit unsigned value.
+The result is NULL if the argument is NULL. The argument is expected to be a
string and (if possible) is treated as one if it is not.
-
+
```
CRC32{ expression)}
```
-
+
* `_expression_`
+
specifies a value expression that determines the values to count. The
-_expression_ cannot contain an aggregate function or a subquery.
+_expression_ cannot contain an aggregate function or a subquery.
See <<expressions,Expressions>>.
-
+
[[examples_of_crc32]]
=== examples of CR32
```
>>SELECT CRC32('Trafodion') from dual;
-
+
(EXPR)
----------
-
+
1960931967
-
+
>>SELECT CRC32(2016) from dual;
-
+
(EXPR)
----------
-
+
2177070256
-
+
```
<<<
[[current_function]]
@@ -3043,7 +3043,7 @@ DEGREES(RADIANS(45))
The DIFF1 function is a sequence function that calculates the amount of
change in an expression from row to row in an intermediate result table
ordered by a sequence by clause in a select statement.
-See <<sequence_by_clause,SEQUENCE BY Clause>>.
+See <<sequence_by_clause,SEQUENCE BY Clause>>.
DIFF1 is a {project-name} SQL extension.
@@ -3593,8 +3593,8 @@ Start/Date Time/Shipped (EXPR)
[[group_concat_function]]
== GROUP_CONCAT Function
-This function returns a string result with the concatenated non-NULL values from a group.
-It returns NULL if there are no non-NULL values.
+This function returns a string result with the concatenated non-NULL values from a group.
+It returns NULL if there are no non-NULL values.
The syntax is as follows:
```
@@ -3604,13 +3604,13 @@ GROUP_CONCAT([DISTINCT] expr [,expr ...]
[SEPARATOR str_val])
```
-Get the concatenated values of expression combinations. To eliminate duplicate values,
-use the DISTINCT clause.
-To sort values in the result, use the ORDER BY clause. To sort in reverse order, add
-the DESC (descending) keyword to the name of the column you are sorting by in the
+Get the concatenated values of expression combinations. To eliminate duplicate values,
+use the DISTINCT clause.
+To sort values in the result, use the ORDER BY clause. To sort in reverse order, add
+the DESC (descending) keyword to the name of the column you are sorting by in the
ORDER BY clause. The default is ascending order; this may be specified explicitly using
-the ASC keyword. The default separator between values in a group is comma (,). To specify
-a separator explicitly, use SEPARATOR followed by the string literal value that should be
+the ASC keyword. The default separator between values in a group is comma (,). To specify
+a separator explicitly, use SEPARATOR followed by the string literal value that should be
inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
[[examples_of_group_concat]]
@@ -3697,7 +3697,7 @@ SALES REP
[[is_ipv4_function]]
== IS_IPV4 Function
-For a given argument, if it is a valid IPV4 string, IS_IPV4() returns 1 else returns 0.
+For a given argument, if it is a valid IPV4 string, IS_IPV4() returns 1 else returns 0.
```
IS_IPV4( expression )
```
@@ -3712,7 +3712,7 @@ See <<expressions,Expressions>>.
[[examples_of_is_ipv4]]
=== Examples of IS_IPV4
-This function returns 1 for the first input argument, since it is a valid IPV4 string;
+This function returns 1 for the first input argument, since it is a valid IPV4 string;
0 for the second input argument, since it is an invalid IPV4 string.
```
@@ -3726,7 +3726,7 @@ This function returns 1 for the first input argument, since it is a valid IPV4 s
[[is_ipv6_function]]
== IS_IPV6 Function
-Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise.
+Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise.
This function does not consider IPv4 addresses to be valid IPv6 addresses.
```
@@ -3743,7 +3743,7 @@ See <<expressions,Expressions>>.
[[examples_of_is_ipv6]]
=== Examples of IS_IPV6
-This function returns 0 for the second input argument, since it is a valid IPV6 string;
+This function returns 0 for the second input argument, since it is a valid IPV6 string;
1 for the second input argument, since it is an invalid IPVr6 string.
```
@@ -3756,8 +3756,8 @@ This function returns 0 for the second input argument, since it is a valid IPV6
+[[inet_aton_function]]
+== INET_ATON Function
+
- +Given the dotted-quad representation of an IPv4 network address as a string,
- +returns an integer that represents the numeric value of the address in network
+ +Given the dotted-quad representation of an IPv4 network address as a string,
+ +returns an integer that represents the numeric value of the address in network
+byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.
+
+```
@@ -3780,15 +3780,15 @@ This function returns 0 for the second input argument, since it is a valid IPV6
+
+(EXPR)
+-----------
- +167773449
+ +167773449
+```
+<<<
+[[inet_ntoa_function]]
+== INET_NTOA Function
+
- +Given a numeric IPv4 network address in network byte order, returns the
- +dotted-quad string representation of the address as a nonbinary string in
- +the connection character set. INET_NTOA() returns NULL if it does
+ +Given a numeric IPv4 network address in network byte order, returns the
+ +dotted-quad string representation of the address as a nonbinary string in
+ +the connection character set. INET_NTOA() returns NULL if it does
+not understand its argument.
+
+```
@@ -3798,15 +3798,15 @@ This function returns 0 for the second input argument, since it is a valid IPV6
+* `_expression_`
++
+specifies an expression that determines the values to include in the
- +conversion of the number to IP address. The _expression_ cannot contain
- +an aggregate function or a subquery. If the input value is NULL, INET_NTOA
- +returns NULL.
+ +conversion of the number to IP address. The _expression_ cannot contain
+ +an aggregate function or a subquery. If the input value is NULL, INET_NTOA
+ +returns NULL.
+See <<expressions,Expressions>>.
+
+[[examples_of_inet_ntoa]]
+=== Examples of INET_NTOA
+
- +this function will convert an integer into the dotted-quad string
+ +this function will convert an integer into the dotted-quad string
+representation of the IP address.
+
+```
@@ -4433,7 +4433,7 @@ SELECT MAX (salary) FROM persnl.employee;
== MD5 Function
Calculates an MD5 128-bit checksum for the string. The value is returned
-as a string of 32 hexadecimal digits, or NULL if the argument was NULL.
+as a string of 32 hexadecimal digits, or NULL if the argument was NULL.
```
MD5( _expression_)
@@ -4446,7 +4446,7 @@ function or a subquery. If the input value is NULL, MD5 returns NULL.
See <<expressions,Expressions>>.
[[examples_of_md5]]
-=== Examples of MD5
+=== Examples of MD5
The return value is a nonbinary string in the connection character set.
```
>>SELECT MD5('testing') from dual;
@@ -6748,9 +6748,9 @@ Start/Date Time/Shipped (EXPR)
[[sha_function]]
== SHA Function
-Calculates an SHA-1 160-bit checksum for the string, as described in
-RFC 3174 (Secure Hash Algorithm). The value is returned as a string of
-40 hexadecimal digits, or NULL if the argument was NULL.
+Calculates an SHA-1 160-bit checksum for the string, as described in
+RFC 3174 (Secure Hash Algorithm). The value is returned as a string of
+40 hexadecimal digits, or NULL if the argument was NULL.
[[examples_of_sha]]
=== examples of SHA
@@ -6766,13 +6766,13 @@ RFC 3174 (Secure Hash Algorithm). The value is returned as a string of
[[sha2_function]]
== SHA2 Function
-Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384,
-and SHA-512). The first argument is the cleartext string to be hashed.
-The second argument indicates the desired bit length of the result, which
+Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384,
+and SHA-512). The first argument is the cleartext string to be hashed.
+The second argument indicates the desired bit length of the result, which
must have a value of 224, 256, 384, 512.
-If either argument is NULL or the hash length is not one of the permitted values,
-the return value is NULL. Otherwise, the function result is a hash value containing
-the desired number of bits. See the notes at the beginning of this section
+If either argument is NULL or the hash length is not one of the permitted values,
+the return value is NULL. Otherwise, the function result is a hash value containing
+the desired number of bits. See the notes at the beginning of this section
about storing hash values efficiently.
[[examples_of_sha2]]
@@ -7780,20 +7780,20 @@ Here, HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minute
```
TO_TIME ('17:05:01', 'HH24:MI:SS')
```
-
+
<<<
[[to_timestamp_function]]
== TO_TIMESTAMP Function
The TO_TIMESTAMP function converts a character value to a timestamp.
-
+
```
TO_TIMESTAMP(character-expression)
```
* `_character-expression_`
+
-is an expression that gives a character value. The expression ia assumed to have the format YYYY-MM-DD HH:MI:SS[.FFFFFFF],
+is an expression that gives a character value. The expression ia assumed to have the format YYYY-MM-DD HH:MI:SS[.FFFFFF],
where YYYY is a 4-digit year, MM is a 2-digit month, DD is a 2-digit day, HH is a 2-digit hours field, MI is a 2-digit
minutes field, SS is a two-digit seconds field and FFFFFF is an optional microseconds field.
@@ -7805,7 +7805,7 @@ minutes field, SS is a two-digit seconds field and FFFFFF is an optional microse
```
TO_TIMESTAMP ('2016-12-07 10:01:00')
```
-
+
<<<
[[translate_function]]
== TRANSLATE Function
@@ -8058,7 +8058,7 @@ characters to upshift. See
<<character_value_expressions,Character Value Expressions>>.
[[examples_of_upshift]]
-=== Examples of UPSHIFT
+=== Examples of UPSHIFT
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return a result in uppercase and
@@ -8405,4 +8405,3 @@ NULL.
```
ZEROIFNULL (salary)
```
-
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/869954c3/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 80ed9e2..2918da5 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -6642,7 +6642,7 @@ sql-query is:
| query-expr-and-order
query-specification is:
-with-clause
+[with-clause]
SELECT [ "[" ANY N "]" | "[" FIRST N "]" ] [ALL | DISTINCT] select-list
FROM table-ref [,table-ref]...
[WHERE search-condition]
@@ -6653,7 +6653,7 @@ SELECT [ "[" ANY N "]" | "[" FIRST N "]" ] [ALL | DISTINCT] select-list
[,colname [ASC[ENDING] | DESC[ENDING]]]...]
[GROUP BY {colname | colnum} [,{colname | colnum}]...]
[HAVING search-condition]
- [access-clause ]
+ [access-clause]
[mode-clause]
With-clause is:
[6/6] incubator-trafodion git commit: [TRAFODION-2522] Fix WITH
syntax diagram
Posted by db...@apache.org.
[TRAFODION-2522] Fix WITH syntax diagram
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a010b2ec
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a010b2ec
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a010b2ec
Branch: refs/heads/master
Commit: a010b2ec963be72e41319932aaf8ab0f2e25b7cf
Parents: 5d21a1e
Author: Dave Birdsall <db...@apache.org>
Authored: Mon Mar 20 20:38:03 2017 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Mon Mar 20 20:38:03 2017 +0000
----------------------------------------------------------------------
docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a010b2ec/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 2918da5..d712dec 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -6657,7 +6657,7 @@ SELECT [ "[" ANY N "]" | "[" FIRST N "]" ] [ALL | DISTINCT] select-list
[mode-clause]
With-clause is:
-with_clause_elements
+WITH with_clause_elements
with_clause_elements is:
with_clause_element [,with_clause_element] \u2026