Using the Names of Built-In Functions as Column Names

The following two examples show a workaround for using a built-in function as a column name in a SELECT statement. This workaround applies to the aggregate functions (AVG, COUNT, MAX, MIN, SUM) as well as the function expressions (algebraic, exponential and logarithmic, time, hex, length, dbinfo, trigonometric, and trim functions).

Using avg as a column name causes the next example to fail because the database server interprets avg as an aggregate function rather than as a column name:

SELECT avg FROM mytab; -- fails

If the DELIMIDENT environment variable is set, you could use avg as a column name as the following example shows:

SELECT "avg" from mytab -- successful

The workaround in the following example removes ambiguity by including a table name with the column name:

SELECT mytab.avg FROM mytab; 

If you use the keyword TODAY, CURRENT, or USER as a column name, ambiguity can occur, as the following example shows:

CREATE TABLE mytab (user char(10),
   CURRENT DATETIME HOUR TO SECOND,TODAY DATE);

INSERT INTO mytab VALUES('josh','11:30:30','1/22/1998');

SELECT user,current,today FROM mytab;

The database server interprets user, current, and today in the SELECT statement as the built-in functions USER, CURRENT, and TODAY. Thus, instead of returning josh, 11:30:30,1/22/1998, the SELECT statement returns the current user name, the current time, and the current date.

If you want to select the actual columns of the table, you must write the SELECT statement in one of the following ways:

SELECT mytab.user,  mytab.current,  mytab.today FROM mytab;

EXEC SQL select * from mytab;