# CHAPTER 9: Expressions

## 9.1 Introduction

An expression (expr) is not a command. It represents an expression that can appear as part of a command. Expressions may appear, for example, in the `SELECT` or `UPDATE` commands. Expressions may also appear in `WHERE` clauses.

Syntax

  `expr is:` | `|attr |string CONSTANT |number |(expr) |CONVERT expr [TO] data_type |CONVERT expr [TO] GENERIC data_type |expr CONVERT [TO] data_type |expr CONVERT [TO] GENERIC data_type |expr operator expr |expr operator |operator expr |built_in_function |user_defined_function |user_defined_aggregate_function |aggregate_function |math_library_function` | `| | | | | | | | | | | | | | | |`

Notes

1. Implicit data type conversions occur during the evaluation of expressions whenever possible

2. Unless the result of an expression is explicitly converted to a data type, the result is generic data (data not governed by data type parameters and not associated with any attribute). Generic data is discussed in the Data Types of this manual.

3. Explicit conversion to data types is discussed in the Empress SQL: User's Guide.

4. The use of expressions for date arithmetic is described in the Empress SQL: User's Guide.

5. An error message will be returned if an expression is impossible to evaluate, for example, division by zero.

6. `operator` is an Empress Operators. Please refer to the next section for the available operators.

7. Empress functions can be divided into:
   - Aggregate Function (`aggregate_function`),
   - Build-In Function (`built_in_function`),
   - Math Library Function (`math_library_function`),
   - User Defined Function (`user_defined_function`) and
   - User Defined Aggregate Function (`user_defined_aggregate_function`).

   The detail descriptions of these functions are described in the next chapter.

   The Aggregate Function are `COUNT`, `MAX`, `MIN`, `SUM`, `AVG`.

   The following is a list of Built-In Function:

   abs ( ) ceiling ( ) datenext ( ) floor ( ) is_decimal ( ) is_integer ( ) is_white ( ) | length ( ) lpad ( ) lscan ( ) ltrim ( ) nullval ( ) round ( ) | rpad ( ) rscan ( ) rstrindex ( ) rtrim ( ) sign ( ) strdel ( ) | strindex ( ) strins ( ) substr ( ) tolower ( ) toupper ( ) trunc ( )

   The available Math library Function vary with the system being used. They will be a subset of the following:

   acos asin atan atan2 ceil cos cosh erf erfc | expr fabs floor fmod frexp frexpman frexpexp gamma hypot | j0 j1 jn jdexp log log10 modf modffrac modfint | pow sin sinh sqrt tan tanh y0 y1 yn

   A User Defined Function or User Defined Aggregate Function is a function defined in accordance with the details specified in the Empress: User Defined Functions manual.

8. Aggregate Functions can not be used within the expressions for `UPDATE` and `WHERE` clause.

## 9.2 Operators

The following is a list of the available Empress Operators:

Table 9-1: Empress Operators

Operators | Description
`+` | addition of numeric data, for example:
       SELECT numeric_attr1 + numeric_attr2 FROM table_name;
`-` | subtraction of numeric data
`*` | product of numeric data
`/` | division of numeric data
`%` | remainder after division of numeric data, for example:
       SELECT numeric_attr1 % numeric_attr2 FROM table_name;
`concat` | concatenation of character data, for example:
       SELECT char_attr1 CONCAT char_attr2 FROM table_name;

       SELECT char_attr1 CONCAT " - " CONCAT char_attr2 FROM table_name;
`day`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic, for example:
       SELECT date_attribute + 3 DAYS FROM table_name;

       SELECT date_attribute - 3 DAYS FROM table_name;
`dayof` | day of `DATE`/`TIME`/`MICROTIMESTAMP` data type
`week`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic
`month`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic
`monthof` | month of `DATE`/`TIME`/`MICROTIMESTAMP` data type
`year`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic
`yearof` | year of `DATE`/`TIME`/`MICROTIMESTAMP` data type
`hour`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic
`hourof` | hour of `TIME`/`MICROTIMESTAMP` data type
`minute`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` arithmetic
`minuteof` | minute of `TIME`/`MICROTIMESTAMP` data type
`second`(`s`) | for `DATE`/`TIME`/`MICROTIMESTAMP` data type
`secondof` | second of `TIME`/`MICROTIMESTAMP` data type
`weekofyear` | week number within the week - ranges from 0-53
`dayname` | name of the day of an integer between 0 and 6 or expression
`dayofweek` | day of the week of a `DATE`/`TIME`/`MICROTIMESTAMP` data type
`dayofyear` | day of the year of a `DATE`/`TIME`/`MICROTIMESTAMP` data type

Please refer to Empress: SQL User's Guide under Date Arithmetic for more examples on the date/time operators.