Expressions in the Select List

You can use any basic type of expression (column, constant, built-in function, aggregate function, and user-defined routine), or combination thereof, in the select list. The expression types are described in Expression. Sections that follow present examples of simple expression in the select list.

You can combine simple numeric expressions by connecting them with arithmetic operators for addition, subtraction, multiplication, and division. If you combine a column expression and an aggregate function, however, you must include the column expression in the GROUP BY clause. (See also Dependencies between the GROUP BY and Projection clauses.)

In general, you cannot use variables (for example, host variables in an ESQL/C application) in the select list by themselves. A variable is valid in the select list, however, if an arithmetic or concatenation operator connects it to a constant.

In a FOREACH SELECT statement, you cannot use SPL variables in the select list, by themselves or with column names, when the tables in the FROM clause are remote tables. You can use SPL variables by themselves or with a constant in the select list only when the tables in the FROM clause are local tables.

In distributed queries of HCL OneDB™, values in expressions (and returned by expressions) are restricted, as Data Types in Cross-Server Transactions describes. Any UDRs whose return values are used as expressions in other databases of the same HCL OneDB instance must be defined in each participating database.

The Boolean operator NOT is not valid in the Projection clause.