Expression

Data values in SQL statements must be represented as expressions. An expression is a specification, which can include operators, operands, and parentheses, that the database server can evaluate to one or more values, or to a reference to some database object.

Expressions can refer to values already in a table of the database, or to values derived from such data, but some expressions (such as TODAY, USER, or literal values) can return values that are independent of the database. You can use expressions to specify values in data-manipulation statements, to define fragmentation strategies, and in other contexts. Use the Expression segment whenever you see a reference to an expression in a syntax diagram.

In most contexts, however, you are restricted to expressions whose returned value is of some specific data type, or of a data type that can be converted by the database server to some required data type.

IBM® Informix® database servers support the following categories of expressions:
Read syntax diagramSkip visual syntax diagram
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

|--+- + -+------------------------------------------------------|
   +- - -+   
   +- * -+   
   +- / -+   
   '- || '   

Notes:
  1. See CAST Expressions
  2. See Column Expressions
  3. See Conditional Expressions
  4. See Constant Expressions
  5. See Constructor Expressions
  6. See Function Expressions
  7. See Statement-Local Variable Expressions
  8. See Aggregate Expressions
  9. Stored Procedure Language only
Element Description Restrictions Syntax
SPL_variable In an SPL routine, a variable that contains some expression type that the syntax diagram shows Must conform to the rules for expressions of that type Identifier
variable Host or program variable that contains some expression type that the syntax diagram shows Must conform to the rules for expressions of that type Language-specific rules for names

Usage

The following table lists the types of SQL expressions and describes what each type returns.
Table 1. SQL expressions
Expression Type Description
Aggregate functions Returns values from built-in or from user-defined aggregates
Arithmetic operators Supports arithmetic operations on one (unary operators) or two (binary operators) numeric operands
Concatenation operator Concatenates two string values
Cast operators Explicit casts from one data type to another
Column expressions Column values
Conditional expressions Returns values that depend on conditional tests
Constant expressions Literal values in data manipulation (DML) statements
Constructor expressions Dynamically creates values for complex data types
Function expressions Returns values from built-in or user-defined functions
Statement-Local Variable expressions References a statement-local variable (SLV) in the same SQL statement where it was declared

You can also use host variables or SPL variables as expressions.