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).
SELECT avg FROM mytab; -- fails
SELECT "avg" from mytab; -- successful
SELECT mytab.avg FROM mytab;
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™.
SELECT mytab.user, mytab.current, mytab.today FROM mytab; EXEC SQL select * from mytab;