SELECT Expressions

The projection-list of a SELECT clause may contain column names, constants and/or expressions. It is sometimes advantageous to use an expression that incorporates data values from one or more columns. Any legal arithmetic expression involving column values, numeric or string constants, functions, and arithmetic operators is permitted in an expression. See Functions In SELECT Clauses. Parentheses may be used as necessary to define evaluation order.

Column names generally match those specified in the SELECT clause. In the case of expressions or column names made up of multiple words (column names over 30 characters long), the XDB Server substitutes the special name Columnn where the second n is the column number -- counting from the left side of the result display.

Numeric constants and string constants can be "selected" just like column names. String constants can be enclosed in single or double quotes. For instance, the query below uses a string constant in the SELECT statement:

SELECT "Supplier", sname, "is located in", city
FROM supplier

The previous query produces the following results display:

Supplier sname Column3 city
Supplier SMITH is located in LONDON
Supplier JONES is located in PARIS
Supplier BLAKE is located in PARIS
Supplier CLARK is located in LONDON
Supplier ADAMS is located in ATHENS

The query below uses an arithmetic expression in the SELECT clause to display shipping costs for each part received from London:

SELECT pno, weight * 1.15
FROM part
WHERE city = "LONDON"

The column rename feature can assign a name to a new column of output computed from an arithmetic expression. For example, the results of the following query would appear under two separate columns named PNO and shipcost:

SELECT pno, shipcost = weight * 1.15
FROM part
WHERE city = "LONDON"
Note:

A NULL value generates a NULL result when used in arithmetic expressions other than aggregate functions. Consider the expression x * y, where the "x" value is NULL. For any "y" value, the XDB Server evaluates this entire expression as NULL. In contrast, NULL values are ignored when determining the results of aggregate functions. For example, consider the query:

SELECT SUM(qty)
FROM partsupp

If the ith record data value in the qty column contains a NULL value, the SUM(qty) function is calculated as if the ith record does not exist.

More: