The sections that follow describe SQL expressions, which are specifications that return one or more values or references to database objects. IBM Informix database servers support the following categories of expressions:

SQL Expressions:
 
   .-Binary Operators------------------------------------------.
   V                                (1)                        |
|----+----+--+-| Cast Expressions |--------------------------+-+--|
     +- --+  |                        (2)                    |
     '- +-'  +-| Column Expressions |------------------------+
             |                             (3)               |
             +-| Conditional Expressions |-------------------+
             |                          (4)                  |
             +-| Constant Expressions |----------------------+
             |                             (5)               |
             +-| Constructor Expressions |-------------------+
             |                          (6)                  |
             +-| Function Expressions |----------------------+
             |                                          (7)  |
             +-| Statement-Local Variable Expressions |------+
             |                           (8)                 |
             +-| Aggregate Expressions |---------------------+
             +-NULL------------------------------------------+
             +-variable--------------------------------------+
             |  (9)                                          |
             +--------SPL_variable---------------------------+
             '-(-| Expression |-)----------------------------'
 
Binary Operators:
 
|--+- +--+------------------------------------------------------|
   +- ---+
   +- *--+
   +- /--+
   '- ||-'
 

1. See page Cast Expressions (IDS)
2. See page Column Expressions
3. See page Conditional Expressions
4. See page Constant Expressions
5. See page Constructor Expressions (IDS)
6. See page Function Expressions
7. See page Statement-Local Variable Expressions (IDS)
8. See page Aggregate Expressions
9. Stored Procedure Language only

The following table lists the types of SQL expressions, as identified in the diagram for Expression, and describes what each type returns.

You can also use host variables or SPL variables as expressions. For a complete list with page references to this chapter, see the following "List of Expressions."

Each category of SQL expression includes many individual expressions. The following table lists all the SQL expressions (and some operators) in alphabetical order. The columns in this table have the following meanings:

• Name gives the name of each expression.
• Description gives a short description of each expression.
• Syntax lists the page that shows the syntax of the expression.
• Usage shows the page that describes the usage of the expression.

Each expression listed in the following table is supported on all database servers unless otherwise noted. When an expression is not supported on all database servers, the Name column notes in parentheses the database server or servers that do support the expression.

Sections that follow describe the syntax and usage of each expression that appears in the preceding table.

Binary arithmetic operators can combine expressions that return numbers.

The following examples use binary arithmetic operators:

quantity * total_price 
price * 2 
COUNT(*) + 2 

If you combine a DATETIME value with one or more INTERVAL values, all the fields of the INTERVAL value must be present in the DATETIME value; no implicit EXTEND function is performed. In addition, you cannot use YEAR to MONTH intervals with DAY to SECOND intervals. For additional information about binary arithmetic operators, see the IBM Informix Guide to SQL: Reference.

The binary arithmetic operators have associated operator functions, as the preceding table shows. Connecting two expressions with a binary operator is equivalent to invoking the associated operator function on the expressions. For example, the following two statements both select the product of the total_price column and 2. In the first statement, the * operator implicitly invokes the times( ) function.

SELECT (total_price * 2) FROM items 
   WHERE order_num = 1001
SELECT times(total_price, 2) FROM items
   WHERE order_num = 1001

You cannot use arithmetic operators to combine expressions that use aggregate functions with column expressions.

The database server provides the operator functions associated with the relational operators for all built-in data types. You can define new versions of these operator functions to handle your own user-defined data types.

For more information, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

The database server also supports the following unary arithmetic operators.

The unary arithmetic operators have the associated operator functions that the preceding table shows. You can define new versions of these functions to handle your own user-defined data types. For more information on this topic, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

If any value that participates in an arithmetic expression is NULL, the value of the entire expression is NULL, as the following example shows:

SELECT order_num, ship_charge/ship_weight FROM orders
   WHERE order_num = 1023

If either ship_charge or ship_weight is NULL, the value returned for the expression ship_charge/ship_weight is also NULL. If the NULL expression ship_charge/ship_weight is used in a condition, its truth value cannot be TRUE, and the condition is not satisfied (unless the NULL expression is an operand of the IS NULL operator).

You can use the concatenation operator ( || ) to concatenate two expressions. These examples show some possible concatenated-expression combinations.

• The first example concatenates the zipcode column to the first three letters of the lname column.
• The second example concatenates the suffix .dbg to the contents of a host variable called file_variable.
• The third example concatenates the value that the TODAY operator returns to the string Date.

  lname[1,3] || zipcode 
  
  :file_variable || '.dbg'
  
  'Date:' || TODAY  

You cannot use the concatenation operator in an embedded-language-only statement. The ESQL/C-only statements of SQL appear in the following list:

You can use the concatenation operator in a SELECT, INSERT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statement within the DECLARE statement.

You can use the concatenation operator in the SQL statement or statements in the PREPARE statement.

The concatenation operator ( || ) has an associated operator function called concat( ). You can define a concat( ) function to handle your own string-based user-defined data types. For more information, see IBM Informix User-Defined Routines and Data Types Developer's Guide.

You can use the CAST AS keywords or the double-colon cast operator ( :: ) to cast an expression to another data type. Both the operator and the keywords invoke a cast from the data type of the expression to the target data type.

To invoke an explicit cast, you can use either the cast operator or the CAST AS keywords. If you use the cast operator or the CAST AS keywords, but no explicit or implicit cast was defined to perform the conversion between two data types, the statement returns an error.

Cast Expressions:
 
                             (1)
|--+-CAST--(--| Expression |-------+--------------------------+--AS--target_data_type--)-+--|
   |                               | .----------------------. |                          |
   |                               | V                      | |                          |
   |                               '---::--target_data_type-+-'                          |
   |                      .----------------------.                                       |
   |                (1)   V                      |                                       |
   '-| Expression |---------::--target_data_type-+---------------------------------------'
 

1. See page Expression