Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:

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:

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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX