Repository: incubator-trafodion Updated Branches: refs/heads/master b03aa4fd6 -> c5519b495
Add Division By for *Create Table Statement* and Fix some typos Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/87a99375 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/87a99375 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/87a99375 Branch: refs/heads/master Commit: 87a993756b21a9efae2a6ebe09afcde792ca01cf Parents: 89433fb Author: liu.yu <[email protected]> Authored: Fri Nov 10 17:08:30 2017 +0800 Committer: liu.yu <[email protected]> Committed: Fri Nov 10 17:08:30 2017 +0800 ---------------------------------------------------------------------- .../sql_functions_and_expressions.adoc | 4 +- .../src/asciidoc/_chapters/sql_statements.adoc | 76 +++++++++++++++++++- 2 files changed, 76 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/87a99375/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 fc81e08..7861cc3 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 @@ -443,7 +443,7 @@ _int_expr_ to _datetime_expr_ and normalizes the result. ADD_MONTHS is a {projec extension. ``` -ADDMONTHS (datetimeexpr, intexpr [, int2 ]) +ADD_MONTHS (datetimeexpr, intexpr [, int2 ]) ``` * `_datetime_expr_` @@ -2573,7 +2573,7 @@ DATE_PART('year', date'12/05/2006') DATE_PART('day', TIMESTAMP '2006-12-31 11:59:59.999999') ``` -* This function returns the value 201 07. +* This function returns the value 2011 07. + ``` DATE_PART('YEARMONTH', date '2011-07-25') http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/87a99375/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 43a42ae..cce664d 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -2629,6 +2629,7 @@ CREATE [VOLATILE] TABLE [IF NOT EXISTS] table [HBASE_OPTIONS (hbase-options-list)] [LOAD IF EXISTS | NO LOAD] [AS select-query] + [DIVISION BY (division-expr-list)] CREATE [VOLATILE] TABLE [IF NOT EXISTS] table like-spec @@ -2816,6 +2817,38 @@ Considerations for LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS>>. + specifies a select query which is used to populate the created table. A select query can be any SQL select statement. +* `_division-expr-list_` ++ +is a list of expressions that can only refer to primary key or STORE BY columns. + ++ +The followings are allowed expressions in the DIVISON BY clause, they are monotonically increasing expressions: + ++ +** cast((exact_numeric_column[ + const1 ]) / const2 as numeric_data_type) +** DATE_PART('YEAR', date_part_arg ) +** DATE_PART('YEARQUARTER', date_part_arg ) +** DATE_PART('YEARMONTH', date_part_arg ) +** DATE_PART('YEARWEEK', date_part_arg ) + +*** date_part_arg is one of the following: + +**** datetime_col +**** datetime_col + const +**** datetime_col - const +**** ADD_MONTHS(datetime_col, const [, 0]) +**** DATE_ADD(datetime_col, const) +**** DATE_SUB(datetime_col, const) + +** DATE_TRUNC(const, datetime_col) +** DATEDIFF(YEAR, const, datetime_col) +** DATEDIFF(QUARTER, const, datetime_col) +** DATEDIFF(MONTH, const, datetime_col) +** DATEDIFF(WEEK, const, datetime_col) +** LEFT(character_col, const) +** SUBSTR[ING](character_col, 1, const) +** SUBSTR[ING](character_col FROM 1 FOR const) + <<< * `_column data-type_` + @@ -3261,6 +3294,8 @@ statement are ASCENDING, DESCENDING, and PARTITION clauses. CREATE TABLE LIKE is [[create_table_examples]] === Examples of CREATE TABLE +==== Examples of CREATE TABLE + * This example creates a table. The clustering key is the primary key. + ``` @@ -3308,6 +3343,43 @@ SELECT * FROM T WHERE b = 'A'; SELECT * FROM T WHERE b = 'A' (not casespecific); ``` +* This is the 1^st^ example of DIVISION BY usage. ++ +``` +CREATE TABLE call_home_data +(id LARGEINT NOT NULL, +ts TIMESTAMP(6) NOT NULL, +device_status VARCHAR(200), +PRIMARY KEY (id, ts)) +SALT USING 16 PARTITIONS ON (id) +DIVISION BY (date_trunc('day', ts)); +``` + +* This is the 2^nd^ example of DIVISION BY usage. ++ +``` +CREATE TABLE sales1 +(store_id INT NOT NULL, +item_id INT NOT NULL, +sale_date DATE DEFAULT DATE '2000-01-01' NOT NULL, +sale_amt NUMERIC(10,2), +PRIMARY KEY (store_id, item_id, sale_date)) +DIVISION BY (DATEDIFF(YEAR, '2017-11-02', sale_date)); +``` + +* This is the 3^rd^ example of DIVISION BY usage. ++ +``` +CREATE TABLE sales2 +(store_id INT NOT NULL, +item_id INT NOT NULL, +sale_date DATE DEFAULT DATE '2000-01-01' NOT NULL, +sale_amt NUMERIC(10,2), +chcol CHAR(20) NOT NULL, +PRIMARY KEY (store_id, item_id, sale_date, chcol)) +DIVISION BY (SUBSTR(chcol, 1, 5)); +``` + <<< [[create_table_examples_create_table_as]] ==== Examples of CREATE TABLE AS @@ -3384,9 +3456,9 @@ create table t(a int, b) as select c,d from t1 + An error is returned. -In the following example, table t1 is created. Table t2 is created using the CREATE TABLE AS syntax without table attributes: - +* In the following example, table t1 is created. Table t2 is created using the CREATE TABLE AS syntax without table attributes: ++ ``` CREATE TABLE t1 (c1 int not null primary key, c2 char(50));
