Repository: trafodion Updated Branches: refs/heads/master 0d30493de -> 843e089c8
[TRAFODION-3174] Add Syntax for *CREATE TABLE Statement* and *ALTER TABLE Statement* and Fix Typos in *Trafodion SQL Reference Manual* Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/1ceff371 Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/1ceff371 Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/1ceff371 Branch: refs/heads/master Commit: 1ceff3712b8677bf7e53e04c517fc639ee6fe991 Parents: 9c59d78 Author: liu.yu <qwerty...@hotmail.com> Authored: Mon Aug 6 19:48:01 2018 +0800 Committer: liu.yu <qwerty...@hotmail.com> Committed: Mon Aug 6 19:48:01 2018 +0800 ---------------------------------------------------------------------- .../src/asciidoc/_chapters/sql_clauses.adoc | 44 ++++--- .../_chapters/sql_language_elements.adoc | 125 ++++++++++--------- .../src/asciidoc/_chapters/sql_statements.adoc | 19 ++- 3 files changed, 109 insertions(+), 79 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/1ceff371/docs/sql_reference/src/asciidoc/_chapters/sql_clauses.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_clauses.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_clauses.adoc index 450dd9f..2f2b5ad 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_clauses.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_clauses.adoc @@ -62,8 +62,8 @@ default is: specifies the column has no default value. You cannot specify NO DEFAULT in an ALTER TABLE statement. See <<alter_table_statement,ALTER TABLE Statement>>. -[[syntax_for_default_clause]] -=== Syntax for Default Clause +[[syntax_description_for_default]] +=== Syntax Description for Default * `DEFAULT _literal_` + @@ -157,8 +157,12 @@ separating the digits of integer output with colons. (FORMAT 'format-string') | (DATE, FORMAT 'format-string') +``` + ++ +``` +format-string is: -format-string for Date Formats is: YYYY-MM-DD MM/DD/YYYY YY/MM/DD @@ -173,13 +177,19 @@ format-string for Date Formats is: + ``` (FORMAT 'format-string') +``` ++ +``` +format-string is: -format-string for other formats is: XXX 99:99:99:99 -99:99:99:99 ``` +[[syntax_description_of_format]] +=== Syntax Description of Format + * `YYYY-MM-DD` + specifies that the FORMAT clause output format is _year-month-day_. @@ -227,23 +237,26 @@ must be a numeric value. specifies that the FORMAT clause output format is a timestamp. The input must be a numeric value. +[[considerations_for_format]] +=== Considerations for FORMAT + [[considerations_for_date_formats]] -=== Considerations for Date Formats +==== Considerations for Date Formats -The expression preceding the (FORMAT â_format-string_') clause must be +* The expression preceding the (`FORMAT _format-string_`) clause must be a DATE value. -The expression preceding the (DATE, FORMAT _'format-string_') clause +* The expression preceding the (DATE, `FORMAT _format-string_`) clause must be a quoted string in the USA, EUROPEAN, or DEFAULT date format. [[considerations_for_other_formats]] ==== Considerations for Other Formats -For XXX, the expression preceding the (FORMAT _'format-string_') +* For XXX, the expression preceding the (FORMAT `_format-string_`) clause must be a numeric value or a string value. -For 99:99:99:99 and -99:99:99:99, the expression preceding the (FORMAT -_'format-string_') clause must be a numeric value. +* For 99:99:99:99 and -99:99:99:99, the expression preceding the (FORMAT +`_format-string_`) clause must be a numeric value. [[examples_of_format]] === Examples of FORMAT @@ -344,6 +357,9 @@ rows-size is: [ELSE number-rows ROWS] END ``` +[[syntax_description_of_sample]] +=== Syntax Description of SAMPLE + * `RANDOM _percent-size_` + directs {project-name} SQL to choose rows randomly (each row having an @@ -364,7 +380,7 @@ exist in the intermediate result table. Each row is picked with a probability of _r_%, where _r_ is the sample size in PERCENT. Therefore, the actual size of the resulting sample is approximately _r_% of _N_. The number of rows picked follows a binomial distribution with -mean equal to _r_ *c_N_/100. +mean equal to _r_*_N_/100. + If you specify a sample size greater than 100 PERCENT, {project-name} SQL returns all the rows in the result table plus duplicate rows. The @@ -780,9 +796,7 @@ LASERPRINTER, X1 W 42000.00 == SEQUENCE BY Clause The SEQUENCE BY clause of the SELECT statement specifies the order in -which to sort the rows - -of the intermediate result table for calculating sequence functions. +which to sort the rows of the intermediate result table for calculating sequence functions. This option is used for processing time-sequenced rows in data mining applications. See <<select_statement>>. @@ -1055,7 +1069,7 @@ expressions must have compatible data types. For example, in the transpose set TRANSPOSE A,B,C AS V, the expressions A,B, and C have compatible data types. -** `(_expression-list_) [,(_expression-list_)]&8230;` +** `(_expression-list_) [,(_expression-list_)]...` + specifies a list of expressions enclosed in parentheses, followed by another list of expressions enclosed in parentheses, and so on. The http://git-wip-us.apache.org/repos/asf/trafodion/blob/1ceff371/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 7e8bd63..f9e60b0 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_language_elements.adoc @@ -1675,20 +1675,26 @@ ways with arithmetic operators. In this syntax diagram, the data type of a term, factor, or numeric primary is numeric. ``` -numeric-expression` is: +numeric-expression is: numeric-term | numeric-expression + numeric-term | numeric-expression - numeric-term +``` +``` numeric-term is: numeric-factor | numeric-term * numeric-factor | numeric-term / numeric-factor +``` +``` numeric-factor is: [+|-] numeric-primary | [+|-] numeric-primary ** numeric-factor +``` +``` numeric-primary is: unsigned-numeric-literal | column-reference @@ -1933,7 +1939,7 @@ The options above serve the same purposes as they serve when you create a sequen [[examples_of_identity_column]] === Examples of Identity Column -* Example of `ALWAYS` +* Example of `ALWAYS`. + ``` CREATE TABLE identity_employee ( @@ -1954,7 +1960,7 @@ SQL>INSERT INTO identity_employee (id, description) VALUES(999, 'ID=999 and DESC *** ERROR[3428] IDENTITY column ID defined as GENERATED ALWAYS cannot accept values specified by the user. ``` -* Example of `BY DEFAULT` +* Example of `BY DEFAULT`. + ``` CREATE TABLE identity_employee ( @@ -2101,12 +2107,12 @@ code values representing the characters in the string. | [_character-set_ | N] X'_hex-code-value_. . . ' | [_character-set_ | N] X'[_space_. . .]_hex-code-value_[[_space_. . .]_hex-code-value_. . .][_space_. . .]' -_ character-set_` +_character-set_` + -specifies the character set ISO88591 or UTF8. The _character-set_ +specifies the character set ISO88591 or UTF8. The `_character-set_` specification of the string literal should correspond with the character set of the column definition, which is either ISO88591 or UTF8. If you -omit the _character-set specification, {project-name} SQL initially assumes +omit the `_character-set_` specification, {project-name} SQL initially assumes the ISO88591 character set if the string literal consists entirely of 7-bit ASCII characters and UTF8 otherwise. (However, the initial assumption will later be changed if the string literal is used in a @@ -2708,7 +2714,7 @@ qty_on_hand * price BETWEEN 1000.00 AND 10000.00 ``` -* This predicate is true if the part cost is less than $5 or more than $800: +* This predicate is true if the _partcost_ is less than $5 or more than $800: + ``` partcost NOT BETWEEN 5.00 AND 800.00 @@ -3138,52 +3144,55 @@ row-value-constructor is: | row-subquery ``` -* `_in-value-list_` is: -+ ``` +in-value-list is: (expression [,expression_]...) ``` +[[syntax_description_of_in]] +==== Syntax Description of IN + * `_row-value-constructor_` + specifies the first operand of the IN predicate. The first operand can be either of: + -``` -(expression [,expression ]...) -``` +** `_(expression [,expression ]...)_` + is a sequence of SQL value expressions, separated by commas and enclosed in parentheses. + -_expression_ cannot include an aggregate function unless expression is -in a HAVING clause. _expression_ can be a scalar subquery (a subquery -that returns a single row consisting of a single column). See -<<expressions,Expressions>>. +`_expression_` cannot include an aggregate function unless expression is +in a HAVING clause. `_expression_` can be a scalar subquery (a subquery +that returns a single row consisting of a single column). ++ +For more information, see <<expressions,Expressions>>. -* `_row-subquery_` +** `_row-subquery_` + is a subquery that returns a single row (consisting of a sequence of -values). See <<subquery,Subquery>> . +values). ++ +For more information, see <<subquery,Subquery>> . * `_table-subquery_` + is a subquery that returns a table (consisting of rows of columns). The table specifies rows of values to be compared with the row of values -specified by the _row-value-constructor_. The number of values of the -_row-value-constructor_ must be equal to the number of columns in the -result table of the _table-subquery_, and the data types of the values +specified by the `_row-value-constructor_`. The number of values of the +`_row-value-constructor_` must be equal to the number of columns in the +result table of the `_table-subquery_`, and the data types of the values must be comparable. * `_in-value-list_` + is a sequence of SQL value expressions, separated by commas and enclosed -in parentheses. _expression_ cannot include an aggregate function -defined on a column. _expression_ can be a scalar subquery (a subquery +in parentheses. `_expression_` cannot include an aggregate function +defined on a column. `_expression_` can be a scalar subquery (a subquery that returns a single row consisting of a single column). In this case, -the result of the _row-value-constructor_ is a single value. The data +the result of the `_row-value-constructor_` is a single value. The data types of the values must be comparable. The number of expressions in the -_in-value-list_ can have at least 5000 expressions. +`_in-value-list_` can have at least 5000 expressions. <<< @@ -3193,7 +3202,7 @@ _in-value-list_ can have at least 5000 expressions. [[logical_equivalent_using_any_or_some]] ===== Logical Equivalent Using ANY (or SOME) -The predicate _expr_ IN (_expr1_, _expr2_, … ) is true if and only +The predicate `_expr_` IN (`_expr1_ , _expr2_, ...` ) is true if and only if the following predicate is true: ``` @@ -3205,17 +3214,17 @@ expr = ANY (expr1, expr2, ... ) The IN predicate is true if and only if either of these is true: -* The result of the _row-value-constructor_ (a row or sequence of +* The result of the `_row-value-constructor_` (a row or sequence of values) is equal to any row of column values specified by -_table-subquery_. +`_table-subquery_`. A table subquery is a query expression and can be specified as a form of a simple table; for example, as the VALUES keyword followed by a list of row values. See <<select_statement,SELECT Statement>>. -* The result of the _row-value-constructor_ (a single value) is equal to +* The result of the `_row-value-constructor_` (a single value) is equal to any of the values specified by the list of expressions -_in-value-list_. +`_in-value-list_`. + In this case, it is helpful to think of the list of expressions as a one-column tableâa special case of a table subquery. The degree of the @@ -3312,8 +3321,8 @@ IN (VALUES ('CLARK', 500), ('GREEN', 200)); The LIKE predicate searches for character strings that match a pattern. -[[like_syntax]] -==== Syntax +[[syntax_description_of_like]] +==== Syntax Description of LIKE ``` match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] @@ -3322,7 +3331,7 @@ match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] * `_match-value_` + is a character value expression that specifies a set of strings to -search for that match the _pattern_. +search for that match the `_pattern_`. * `_pattern_` + @@ -3333,13 +3342,13 @@ the search. + is a character value expression that must evaluate to a single character. The escape character value is used to turn off the special -meaning of percent (%) and underscore (_). See <<wild_card_characters,Wild-Card Characters>>. -<<escape_characters>>. - -See <<character_value_expressions,Character Value Expressions>>. +meaning of percent (%) and underscore (_). ++ +For more information, see <<wild_card_characters,Wild-Card Characters>>. +<<escape_characters>> and <<character_value_expressions,Character Value Expressions>>. -[[like_considerations]] -==== Considerations +[[considerations_for_like]] +==== Considerations for Like [[comparing_the_value_to_the_pattern]] ===== Comparing the Value to the Pattern @@ -3458,13 +3467,13 @@ part of the string to search for, not a wild-card character. [[regexp_predicate]] === REGEXP Predicate -Performs a pattern match of a string expression against a pattern . +Performs a pattern match of a string expression against a pattern. The pattern can be an extended regular expression. Returns 1 if expression matches pattern; otherwise it returns 0. If either expression or pattern is NULL, the result is NULL. -[[regexp_syntax]] -==== Syntax +[[syntax_description_of_regexp]] +==== Syntax Description of REGEXP ``` match-value [NOT] REGEXP regular-expression ``` @@ -3472,36 +3481,34 @@ match-value [NOT] REGEXP regular-expression * `_match-value_` + is a character value expression that specifies a set of strings to -search for that match the _regular-expression_. +search for that match the `_regular-expression_`. + +* `NOT` ++ +If you specify `NOT`, the predicate is true if the `_regular-expression_` does not +match any string in the `_match-value_`. * `_regular-expression_` + is a character value expression that specifies a regular expression. Trafodion regular expressions follow POSIX regular expression rules. - -[[using_not]] -===== Using NOT - -If you specify NOT, the predicate is true if the _regular-expression_ does not -match any string in the _match-value_. - [[regexp_examples]] ==== Examples -* Find valid numbers +* Find valid numbers. + ``` col REGEXP '^[0-9]*\s*$' ``` -* Find valid words, no numbers +* Find valid words, no numbers. + ``` col REGEXP '^.[A-Za-z]+\s*$' ``` -* Find valid email address +* Find valid email address. + ``` col REGEXP '\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*\s*' @@ -3526,10 +3533,8 @@ row-value-constructor is: + specifies the operand of the NULL predicate. The operand can be either of these: -+ -``` -(expression [,expression ]...) -``` + +** `(expression [,expression ]...)` + is a sequence of SQL value expressions, separated by commas and enclosed in parentheses. @@ -3539,7 +3544,7 @@ in a HAVING clause. _expression_ can be a scalar subquery (a subquery that returns a single row consisting of a single column). See <<expressions,Expressions>>. -* `_row-subquery_` +** `_row-subquery_` + is a subquery that returns a single row (consisting of a sequence of values). See <<subquery,Subquery>> . @@ -3950,7 +3955,7 @@ surrounding predicates or search conditions are true. * `NOT` + reverses the truth value of its operandâthe following predicate or -search condition. predicate is a BETWEEN, comparison, EXISTS, IN, LIKE, NULL, or quantified +search condition. Predicate is a BETWEEN, comparison, EXISTS, IN, LIKE, NULL, or quantified comparison predicate. A predicate specifies conditions that must be satisfied for a row to be chosen. See <<predicates,Predicates>> and individual entries. @@ -4066,7 +4071,7 @@ a subquery. When a subquery is used to provide comparison values, the SELECT statement that contains the subquery is called an outer query. The -subquery within the SELECT is called an _inner query_. +subquery within the SELECT is called an *inner query*. In this case, the differences between the SELECT statement and the SELECT form of a subquery are: http://git-wip-us.apache.org/repos/asf/trafodion/blob/1ceff371/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 cc6d17f..0b773b5 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -411,6 +411,7 @@ data-type is: | nchar varying (length [characters]) [UPSHIFT] [[NOT] CASESPECIFIC] | numeric [(precision [,scale])] [signed|unsigned] + | tinyint [signed|unsigned] | smallint [signed|unsigned] | int[eger] [signed|unsigned] | largeint @@ -2093,7 +2094,7 @@ CREATE LIBRARY [[catalog-name.]schema-name.]library-name [[create_library_syntax]] === Syntax Description of CREATE LIBRARY -* `\[[_catalog-name_.]_schema-name_.]_library-name_` +* `_[[catalog-name.]schema-name.]library-name_` + specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify a name that is unique and does not exist for @@ -3064,6 +3065,7 @@ data-type is: | nchar varying (length [characters]) [upshift] [[not] casespecific] | numeric [(precision [,scale])] [signed|unsigned] + | tinyint [signed|unsigned] | smallint [signed|unsigned] | int[eger] [signed|unsigned] | largeint @@ -6144,8 +6146,10 @@ GRANT {privilege-name [,privilege-name]... |ALL [PRIVILEGES]} TO grantee [WITH GRANT OPTION] [[GRANTED] BY grantor] +``` -privilege is: +``` +privilege-name is: DELETE | EXECUTE | INSERT [column-list] @@ -6153,23 +6157,30 @@ privilege is: | SELECT [column-list] | UPDATE [column-list] | USAGE +``` -object-type is: +``` +object-type is: FUNCTION | PROCEDURE | LIBRARY | SEQUENCE | TABLE +``` +``` grantee is: auth-name +``` +``` grantor is: role-name +``` +``` column-list is: (column [,colummn] ...) - ``` <<<
