cherry pick add alter column 2

Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/08b41edb
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/08b41edb
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/08b41edb

Branch: refs/heads/master
Commit: 08b41edb08d313ed524e64392271d17c38ee9b19
Parents: a465ec8
Author: liu.yu <yu....@esgyn.cn>
Authored: Mon Aug 14 11:56:00 2017 +0800
Committer: liu.yu <yu....@esgyn.cn>
Committed: Tue Aug 22 11:44:19 2017 +0800

----------------------------------------------------------------------
 .../sql_functions_and_expressions.adoc          |  2 +-
 .../src/asciidoc/_chapters/sql_statements.adoc  | 62 +++++++++++++++++---
 2 files changed, 54 insertions(+), 10 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/08b41edb/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 27d9f2c..fc81e08 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
@@ -2523,7 +2523,7 @@ timestamp or a date expression.
 DATE_PART is a {project-name} extension.
 
 ```
-DATEPART(text, datetime-expr)
+DATE_PART(text, datetime-expr)
 ```
 
 * `_text_`

http://git-wip-us.apache.org/repos/asf/trafodion/blob/08b41edb/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 354a3c3..e8f446e 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -377,21 +377,19 @@ To run this statement, AUTOCOMMIT must be turned ON (the 
default) for the sessio
 ALTER TABLE name alter-action
 
 alter-action is:
-
      ADD [IF NOT EXISTS][COLUMN] column-definition
    | ADD [CONSTRAINT constraint-name] table-constraint
    | DROP CONSTRAINT constraint-name [RESTRICT]
    | RENAME TO new-name
    | DROP COLUMN [IF EXISTS] column-name
+   | ALTER COLUMN column-definition
 
 column-definition is:
-
    column-name data-type
       ([DEFAULT default]
          [[constraint constraint-name] column-constraint])
 
 data-type is:
-
      char[acter] [(length)[characters]]
          [CHARACTER SET char-set-name]
          [UPSHIFT] [[not] casespecific]
@@ -418,7 +416,6 @@ data-type is:
    | interval { start-field to end-field | single-field }
 
 default is:
-
      literal
    | null
    | currentdate
@@ -426,24 +423,20 @@ default is:
    | currenttimestamp }
 
 column-constraint is:
-
      not null
    | unique
    | check (condition)
    | references ref-spec
 
 table-constraint is:
-
      unique (column-list)
    | check (condition)
    | foreign key (column-list) references ref-spec
 
 ref-spec is:
-
    referenced-table [(column-list)]
 
 column-list is:
-
    column-name[, column-name]...
 ```
 
@@ -616,6 +609,16 @@ the clauses for the _column-definition_ are the same as 
described in add [column
 +
 drops the specified column from _table_, including the column’s data. you 
cannot drop a primary key column.
 
+* `alter column _column-definition_`
++
+alters the data-type of a column in an existing Trafodion table. The following 
conditions have to be met, else SQL error 1404 will be returned.
+
+** the old (existing) column datatype and new column datatype must be VARCHAR 
(variable length string datatype).
+** new column length must be greater than or equal to old column length.
+** old and new character sets must be the same.
+** altered columns cannot be part of primary key or secondary index key.
+** table cannot be a VOLATILE table.
+
 <<<
 [[alter_table_considerations]]
 === Considerations for ALTER TABLE
@@ -655,7 +658,7 @@ If the constraint refers to the other table in a query 
expression, you must also
 [[alter_table_examples]]
 ===  Example of ALTER TABLE
 
-This example adds a column:
+* This example adds a column.
 
 ```
 ALTER TABLE persnl.project
@@ -663,6 +666,47 @@ ALTER TABLE persnl.project
       NUMERIC (4) UNSIGNED
 ```
 
+
+* This example alters a column of an existing table – showing both positive 
and negative cases.
+
+```
+>>INVOKE T;
+
+-- Definition of Trafodion table TRAFODION.SEABASE.T
+-- Definition current  Wed Jul  8 01:28:40 2015
+
+  (
+    SYSKEY    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+  , A         INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , B         VARCHAR(30) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL
+  )
+
+--- SQL operation complete.
+>>ALTER TABLE T ALTER COLUMN B VARCHAR(40);
+
+--- SQL operation complete.
+>>INVOKE T;
+
+-- Definition of Trafodion table TRAFODION.SEABASE.T
+-- Definition current  Wed Jul  8 01:29:03 2015
+
+  (
+    SYSKEY     LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+  , A          INT NO DEFAULT NOT NULL NOT DROPPABLE
+  , B          VARCHAR(40) CHARACTER SET ISO88591 COLLATE
+      DEFAULT DEFAULT NULL
+  )
+
+--- SQL operation complete.
+>>ALTER TABLE T ALTER COLUMN B VARCHAR(30);
+
+*** ERROR[1404] Datatype for column B cannot be altered.
+
+--- SQL operation failed with errors.
+```
+
+
 <<<
 [[alter_user_statement]]
 == ALTER USER Statement

Reply via email to