CHAPTER 9: Expressions

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.

The following is a list of the available Empress Operators:

Table 9-1: Empress Operators

   SELECT numeric_attr1 + numeric_attr2 FROM table_name;

   SELECT numeric_attr1 % numeric_attr2 FROM table_name;

   SELECT char_attr1 CONCAT char_attr2 FROM table_name;

   SELECT char_attr1 CONCAT " - " CONCAT char_attr2 FROM table_name;

   SELECT date_attribute + 3 DAYS FROM table_name;

   SELECT date_attribute - 3 DAYS FROM table_name;

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