Expressions

---

Expressions are used in a variety of contexts within SQL statements, particularly in search condition predicates and the SET clause in UPDATE statements respectively.

An expression always evaluates to a single value.

Expression Syntax

The syntax of an expression is as follows:

Note: A user-defined-function is created by using the CREATE FUNCTION statement.

Unary Operators

A unary operator operates on only one operand.

The prefix operator + (unary plus) does not change its operand.

The prefix operator - (unary minus) reverses the sign of its operand.

Binary Operators

A binary operator operates on two operands.

The binary operators specify addition (+), subtraction (-), multiplication (*) and division (/) for numerical operands, and concatenation (||) for string operands.

Note: The operand of a binary operator may not be a set function that includes the keyword DISTINCT.

Operands

When a column name is used as an operand, it represents the single value contained in the column for the row currently addressed when the expression is evaluated.

The column name may be qualified by the name of the table or view, see Identifiers.

Evaluating Arithmetical Expressions

Expressions within parentheses are evaluated first. When the order of evaluation is not specified by parentheses, the customary arithmetical rules apply, i.e. multiplication and division are performed before addition and subtraction and operators with the same precedence are applied from left to right.

If any operand in an expression is NULL, the whole expression evaluates to NULL. No other expressions evaluate to NULL. Division by zero results in a run-time error.

Arithmetical expressions with mixed numerical and character data are illegal.

Note: Where host variables are used in expressions, type conversion may result in apparently incompatible data types being accepted, see Data Types in SQL Statements.

The type and precision of the result of an arithmetical expression is determined in accordance with the rules described below. If there are more than two operands in an expression, the type and precision of the result is derived in accordance with the sequence in which the component binary operations are performed.

Formal Evaluation Rules

Formally, the arithmetical rules are summarized as follows:

	FLOAT(p")	INTEGER(p")	DECIMAL(p", s")
FLOAT(p')	FLOAT(p)1	FLOAT(p)a	FLOAT(p)a
INTEGER(p')	FLOAT(p)a	INTEGER(p)2	DECIMAL(p, s)3
DECIMAL(p', s')	FLOAT(p)a	DECIMAL(p, s)c	DECIMAL(p, s)c

1p = max(15, p', p") 2operators +, -: p = min(45, max(p', p")+1) operator *: p = min(45, p'+p") operator /: p = p' 3operator +, -: p = min(45, max(p'-s', p"-s")+max(s', s")+1), s = max(s', s") operator *: p = min(45, p'+p"), s = min(45, s'+s") operator /: p = min(45, max(15, p'+p")), s = p-(p'-s')-s"

Descriptive Evaluation Rules

In descriptive terms, the rules are as follows:

• If any of the operands is floating point, the result is floating point.

  For all arithmetic expressions, the precision of the result is the highest operand precision. However, the precision is never less than 15. For example:

  FLOAT(4)+FLOAT(6) gives FLOAT(15)

  FLOAT(20)-FLOAT(32) gives FLOAT(32)

  FLOAT(4)*FLOAT(4) gives FLOAT(15)

  FLOAT(4)/FLOAT(20) gives FLOAT(20)

• If all the operands are integer, the result is integer.

  For addition and subtraction, the precision of the result is the precision of the highest operand plus 1. However, the precision may not exceed 45.

  For multiplication, the precision of the result is the sum of the precisions of the operands. However, the precision may not exceed 45.

  For division, the precision of the result is the precision of the dividend. For example:

  INTEGER(3)+INTEGER(5) gives INTEGER(6)

  INTEGER(20)-INTEGER(30) gives INTEGER(31)

  INTEGER(5)*INTEGER(18) gives INTEGER(23)

  INTEGER(4)/INTEGER(6) gives INTEGER(4)

• If all the operands are decimal, or decimal and integer operands are mixed, the result is decimal.

  For expressions mixing decimal and integer operands, INTEGER(p) is treated as DECIMAL(p,0).

  For addition and subtraction, the number of positions to the left of the decimal point (i.e. the difference between precision and scale) in the result is the greatest number of positions in any operand plus 1. The scale of the result is the greatest scale of any of the operands. The precision may not exceed 45. For example:

  INTEGER(3)+ DECIMAL(6,3) gives DECIMAL(7,3)

  DECIMAL(4,2)- DECIMAL(8,5) gives DECIMAL(9,5)

  For multiplication, the precision of the result is the sum of the precisions of the operands.

  The scale of the result is the sum of the scales of the operands. Neither the precision nor the scale may exceed 45. If the value of the result does not fit into the precision and scale, overflow occurs. For example:

  INTEGER(3)*DECIMAL(6,3) gives DECIMAL(9,3)

  DECIMAL(4,2)*DECIMAL(8,5) gives DECIMAL(12,7)

  DECIMAL(12,7)*DECIMAL(10,2) gives DECIMAL(22,9)

  DECIMAL(25,0)*DECIMAL(25,25) gives DECIMAL(45,25)

  For division, the precision of the result is the sum of the precisions of the operands. The precision is however never less than 15 and may not exceed 45.

  The scale of the result is calculated as the precision of the result, less the number of positions to the left of the decimal point in the dividend, less the scale of the divisor. An error occurs if this calculation gives a negative value for the scale. For example:

  INTEGER(3)/DECIMAL(6,3) gives DECIMAL(15,9)

  DECIMAL(4,2)/DECIMAL(8,5) gives DECIMAL(15,8)

  DECIMAL(12,7)/DECIMAL(10,2) gives DECIMAL(22,15)

  DECIMAL(25,0)/DECIMAL(25,25) gives DECIMAL(45,0)

  DECIMAL(20,0)/DECIMAL(20,20) gives DECIMAL(40,0)

  DECIMAL(45,0)/DECIMAL(45,45) gives error (scale= -45)

Evaluating String Expressions

The result of a string concatenation expression is a string containing the first operand string directly followed by the second.

The following rules apply:

• If string literals or fixed-length host variables are concatenated, any trailing blanks in the operands are retained.
• If a fixed-length character column value is directly concatenated with another string, any trailing blanks in the column value up to the defined fixed length of the column are retained.
• If a variable-length character column value is directly concatenated with another string, any trailing blanks in the column value up to the actual length of the column value are retained.
• If a fixed-length character value and a variable-length character value are concatenated, the result will be a variable-length character value.
• If a character value and a national character value are concatenated, the result will be a national character value.
• If either of the operands in a concatenation expression is NULL, the result of the expression is NULL.
• When concatenating string expressions, the resulting string's collation depends on whether and where a collation has been specified:
  • If no collation(s) have been specified for the column-definition, in a domain or explicitly in the concatenation statement, then the resulting string has the Mimer SQL default collation. See Character Sets.
  • If one string has a specific collation and the other(s) do not then they are coerced into having the specific collation.
  • If the strings have specific but differing collations, the resulting string has the Mimer SQL default collation.

    For more information, see the Mimer SQL User's Manual, Collations.

Select Specification

A select specification can be used as an expression. This is commonly known as scalar subqueries. A scalar subquery may not return more than one value. The result of an empty subquery is null.

Examples

 SET total = (SELECT COUNT(*) FROM categories)
 
 SELECT c.surname, c.forename,
 (SELECT COUNT(*)
 FROM orders
 WHERE customer_id = c.customer_id) AS orders
 FROM customers AS c
 

The last example shows a correlated subquery i.e. a subquery with a reference to a column in a table not present in the subquery itself.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se