Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Retrieving Computed Values


You can retrieve computed values by using arithmetic, string and boolean operators in the SELECT clause of the statement.

When retrieving computed values, parentheses can be used to force the operation priority.

Without parentheses, the normal precedence rules apply, i.e. first multiplication and division are performed, then addition and subtraction, then comparisons and finally AND and OR. Operators with the same precedence are evaluated from left to right.

The following computational operators may be used:

Operator
Explanation
 +
addition
 -
subtraction
 *
multiplication
 /
division
 ||
string concatenation

See the Mimer SQL Reference Manual, SQL Syntax Elements, for information regarding the type and precision of the result of an arithmetic expression.

Show the exchange rate for the US Dollar if there was a -10% change:
 SELECT exchange_rate, exchange_rate * 0.90
    FROM  currencies
    WHERE code = 'USD';
Returns:

EXCHANGE_RATE
 0.8711
 0.783990

Evaluating Boolean Expressions

Boolean expressions return a truth value (TRUE or FALSE), depending on the result of one or more boolean expressions.

Specify when the exchange rate is less than 1 or the currency code is "ALL":
 SELECT currency,
        exchange_rate < 1.0 or code = 'ALL'
    FROM currencies;
Returns:

 currency
 
 UAE Dirhams
 FALSE
 Afghanis
 FALSE
 Leke
 TRUE
 Armenian Drams
 -
 Netherlands Antillian Guilders
 FALSE
 Kwanza
 -
 Argentine Pesos
 TRUE
 Australian Dollars
 FALSE
 ...
 ...

Labels and Computed Values

The computed column is unnamed by default in the result table. A label may be used to provide a name.

For example:
 SELECT exchange_rate, exchange_rate * 0.90 AS new_exchange_rate
    FROM currencies
    WHERE code = 'USD';
Returns:

EXCHANGE_RATE
NEW_EXCHANGE_RATE
0.8711
0.783990

Constant Values

A column may also be computed as a constant value, which adds an extra column to the result table.

For example:
 SELECT exchange_rate, '10% reduction:',
        exchange_rate * 0.90 AS new_exchange_rate
    FROM currencies
    WHERE code = 'USD';
Returns:

EXCHANGE_RATE
 
NEW_EXCHANGE_RATE
0.8711
 10% reduction:
0.783990

You may also retrieve a value computed using the values in two or more columns, providing that the data types are compatible.

Retrieve the currencies prefixed with the word "Currency:":
 SELECT 'Currency: ' || currency
    FROM currencies
    WHERE code LIKE 'A%';
Returns:

 
 Currency: UAE Dirhams
 Currency: Afghanis
 Currency: Leke
 Currency: Armenian Drams
 Currency: Netherlands Antillian Guilders
 Currency: Kwanza
 ...

Padding Concatenated Strings

For string concatenation, column values are padded with trailing blanks to the length of the column definition, if the column data type is fixed-length (CHARACTER or NATIONAL CHARACTER).

For example:
 SELECT currency || 'Currency'
    FROM currencies
    WHERE code LIKE 'A%';
Returns:

 
 UAE Dirhams                      Currency
 Afghanis                         Currency
 Leke                             Currency
 Armenian Drams                   Currency
 Netherlands Antillian Guilders   Currency
 Kwanza                           Currency
 ...

(If the column data type is variable length, i.e. VARCHAR or NCHAR VARYING, no blank padding is performed.)


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