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 built-in 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, SYSDATE, 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/2008');

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/2008, the SELECT statement returns the current user name, the current time, and the current date. The SYSDATE keyword has a similar effect in databases of HCL OneDB™.

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;