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 Relationship of 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 Dynamic Server, values in expressions (and returned by expressions) are restricted, as Data Types in Distributed Queries (IDS) describes. Any UDRs used as expressions in other databases of the same Dynamic Server instance must be defined in each participating database.

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

Selecting Columns

Column expressions are the most commonly used expressions in a SELECT statement. For a complete description of the syntax and use of column expressions, see Column Expressions. The following examples use column expressions in the Projection clause:

SELECT orders.order_num, items.price FROM orders, items
SELECT customer.customer_num ccnum, company FROM customer
SELECT catalog_num, stock_num, cat_advert [1,15] FROM catalog
SELECT lead_time - 2 UNITS DAY FROM manufact

Selecting Constants

If you include a constant expression in the select list, the same value is returned for each row that the query returns (except when the constant expression is NEXTVAL). For a complete description of the syntax and use of constant expressions, see Constant Expressions. Examples that follow show constant expressions within a select list:

SELECT 'The first name is', fname FROM customer
SELECT TODAY FROM cust_calls
SELECT SITENAME FROM systables WHERE tabid = 1
SELECT lead_time - 2 UNITS DAY FROM manufact
SELECT customer_num + LENGTH('string') from customer

Selecting Built-In Function Expressions

A built-in function expression uses a function that is evaluated for each row in the query. All built-in function expressions require arguments. This set of expressions contains the time functions and the length function when they are used with a column name as an argument. The following examples show built-in function expressions within the select list of the Projection clause:

SELECT EXTEND(res_dtime, YEAR TO SECOND) FROM cust_calls
SELECT LENGTH(fname) + LENGTH(lname) FROM customer
SELECT HEX(order_num) FROM orders
SELECT MONTH(order_date) FROM orders

Selecting Aggregate Function Expressions

An aggregate function returns one value for a set of queried rows. This value depends on the set of rows that the WHERE clause of the SELECT statement qualifies. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms. Examples that follow show aggregate functions in a select list:

SELECT SUM(total_price) FROM items WHERE order_num = 1013
SELECT COUNT(*) FROM orders WHERE order_num = 1001
SELECT MAX(LENGTH(fname) + LENGTH(lname)) FROM customer

Selecting User-Defined Function Expressions

User-defined functions extend the range of functions that are available to you and allow you to perform a subquery on each row that you select.

The following example calls the get_orders( ) user-defined function for each customer_num and displays the returned value under the n_orders label:

SELECT customer_num, lname, get_orders(customer_num) n_orders
      FROM customer

If an SPL routine in a SELECT statement contains certain SQL statements, the database server returns an error. For information on which SQL statements cannot be used in an SPL routine that is called within a query, see Restrictions on SPL Routines in Data-Manipulation Statements.

For the complete syntax of user-defined function expressions, see User-Defined Functions.

Selecting Expressions That Use Arithmetic Operators

You can combine numeric expressions with arithmetic operators to make complex expressions. You cannot combine expressions that contain aggregate functions with column expressions. These examples show expressions that use arithmetic operators within a select list in the Projection clause:

SELECT stock_num, quantity*total_price FROM customer
SELECT price*2 doubleprice FROM items
SELECT count(*)+2 FROM customer 
SELECT count(*)+LENGTH('ab') FROM customer

Selecting ROW Fields (IDS)

You can select a specific field of a named or unnamed ROW type column with row.field notation, using a period ( . ) as a separator between the row and field names. For example, suppose you have the following table structure:

CREATE ROW TYPE one (a INTEGER, b FLOAT)
CREATE ROW TYPE two (c one, d CHAR(10))
CREATE ROW TYPE three (e CHAR(10), f two)

CREATE TABLE new_tab OF TYPE two
CREATE TABLE three_tab OF TYPE three

The following examples show expressions that are valid in the select list:

SELECT t.c FROM new_tab t
SELECT f.c.a FROM three_tab
SELECT f.d FROM three_tab

You can also enter an asterisk ( * ) in place of a field name to signify that all fields of the ROW-type column are to be selected.

For example, if the my_tab table has a ROW-type column named rowcol that contains four fields, the following SELECT statement retrieves all four fields of the rowcol column:

SELECT rowcol.* FROM my_tab

You can also retrieve all fields from a row-type column by specifying only the column name. This example has the same effect as the previous query:

SELECT rowcol FROM my_tab

You can use row.field notation not only with ROW-type columns but with expressions that evaluate to ROW-type values. For more information, see Column Expressions in the Expression segment.