Merge PR 1645 [TRAFODION-3144] Correct Syntactic Descriptions of Character String Data Types and Add Examples (NCHAR) for *ALTER TABLE Statement* in *Trafodion SQL Reference Manual*
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 4b4e9a3..84e8600 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -390,25 +390,27 @@
column-definition is:
column-name data-type
- ([DEFAULT default]
- [[constraint constraint-name] column-constraint])
+ [[DEFAULT default]
+ [[constraint constraint-name] column-constraint]]
data-type is:
- char[acter] [(length)[characters]]
- [CHARACTER SET char-set-name]
- [UPSHIFT] [[not] casespecific]
- | char[acter] varying (length)
- [character set char-set-name]
- [upshift] [[not] casespecific]
- | varchar (length) [character set char-set-name]
- [upshift] [[not] casespecific]
- | varchar2 (length) [character set char-set-name]
- [upshift] [[not] casespecific]
+ char[acter] [(length [characters])]
+ [CHARACTER SET char-set-name]
+ [UPSHIFT] [[NOT] CASESPECIFIC]
+ | char[acter] varying (length [characters])
+ [CHARACTER SET char-set-name]
+ [UPSHIFT] [[NOT] CASESPECIFIC]
+ | varchar (length [characters])
+ [CHARACTER SET char-set-name]
+ [UPSHIFT] [[NOT] CASESPECIFIC]
+ | varchar2 (length [characters])
+ [CHARACTER SET char-set-name]
+ [UPSHIFT] [[NOT] CASESPECIFIC]
+ | nchar [(length [characters])]
+ [UPSHIFT] [[NOT] CASESPECIFIC]
+ | nchar varying (length [characters])
+ [UPSHIFT] [[NOT] CASESPECIFIC]
| numeric [(precision [,scale])] [signed|unsigned]
- | nchar [(length) [character set char-set-name]
- [upshift] [[not] casespecific]
- | nchar varying(length) [character set char-set-name]
- [upshift] [[not] casespecific]
| smallint [signed|unsigned]
| int[eger] [signed|unsigned]
| largeint
@@ -756,6 +758,88 @@
ALTER COLUMN vend_id RENAME TO cstm_id;
```
+* NATIONAL CHAR (or NCHAR) is a short-hand for CHARACTER SET UCS2, so it is not possible to alter the CHARACTER SET when using NCHAR,
+as the following example shows.
+
++
+NOTE: NCHAR or NATIONAL CHAR implicitly assumes that the character set is *UCS2*,
+whether the character set was specified at installation time to be ISO88591 or UTF8,
+and the character set (*UCS2*) of NCHAR or NATIONAL CHAR *cannot be specified* or *altered*.
+This is true for both *ALTER* and *CREATE* statement.
+
++
+```
+SQL>CREATE TABLE test1 (c1 nchar);
+
+--- SQL operation complete.
+
+
+SQL>SHOWDDL test1;
+
+CREATE TABLE TRAFODION.SEABASE.TEST1
+ (
+ C1 CHAR(1) CHARACTER SET UCS2 COLLATE DEFAULT
+ DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+
+
+SQL>ALTER TABLE test1 ALTER COLUMN c1 NCHAR CHARACTER SET ISO88591;
+
+*** ERROR[15001] A syntax error occurred at or before:
+alter table test1 alter column c1 nchar character set iso88591;
+ ^ (47 characters from start of SQL statement) [2018-07-13 10:24:12]
+```
+
+* This example shows how to alter the data type of NCHAR column _c1_.
+
++
+```
+SQL>CREATE TABLE test1 (c1 nchar);
+
+--- SQL operation complete.
+
+
+SQL>SHOWDDL test1;
+
+CREATE TABLE TRAFODION.SEABASE.TEST1
+ (
+ C1 CHAR(1) CHARACTER SET UCS2 COLLATE DEFAULT
+ DEFAULT NULL NOT SERIALIZED
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+
+
+SQL>ALTER TABLE test1 ALTER COLUMN c1 varchar(10) CHARACTER SET ISO88591;
+
+--- SQL operation complete.
+
+
+SQL>SHOWDDL test1;
+
+CREATE TABLE TRAFODION.SEABASE.TEST1
+ (
+ C1 VARCHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL NOT SERIALIZED /*altered_col*/
+ )
+ ATTRIBUTES ALIGNED FORMAT
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+```
+
* The following table _orders_ has two non-unique indexes
(_index_orders1_ and _index_orders2_) and
two unique indexes (_unique_index_orders1_ and _unique_index_orders2_).
