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 quotes. 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);