Using ALL, DISTINCT, or UNIQUE as a Column Name

If you want to use the ALL, DISTINCT, or UNIQUE keywords as column names in a SELECT statement, you can take advantage of a workaround.

First, consider what happens when you try to use one of these keywords without a workaround. In the following example, using all as a column name causes the SELECT statement to fail because the database server interprets all as a keyword rather than as a column name:
SELECT all FROM mytab -- fails;
You must use a workaround to make this SELECT statement execute successfully. If the DELIMIDENT environment variable is set, you can use all as a column name by enclosing all in double quotation marks. In the following example, the SELECT statement executes successfully because the database server interprets all as a column name:
SELECT "all" from mytab; -- successful
The workaround in the following example uses the keyword ALL with the column name all:
SELECT ALL all FROM mytab;

The examples that follow show workarounds for using the keywords UNIQUE or DISTINCT as a column name in a CREATE TABLE statement.

The next example fails to declare a column named unique because the database server interprets unique as a keyword rather than as a column name:
CREATE TABLE mytab (unique INTEGER); -- fails
The following workaround uses two SQL statements. The first statement creates the column mycol; the second statement renames the column mycol to unique:
CREATE TABLE mytab (mycol INTEGER);

RENAME COLUMN mytab.mycol TO unique;
The workaround in the following example also uses two SQL statements. The first statement creates the column mycol; the second alters the table, adds the column unique, and drops the column mycol:
CREATE TABLE mytab (mycol INTEGER);

ALTER TABLE mytab 
   ADD (unique INTEGER),
   DROP (mycol);

Copyright© 2019 HCL Technologies Limited