Expression

Scope

SQL Syntax

  [+ -] {
    ColumnName
    USER
    :HostVariable [[INDICATOR]:IndicatorVariable]
    ?
    :LocalVariable
    :ProcedureParameter
    ::Built-inVariable
    AddMonthsFunction
    AggregateFunction
    Constant
    DateTimeFunction
    CurrentFunction
    LongColumnFunction
    StringFunction
    CASTFunction
    (Expression)
    TIDFunction
  }

  [ { * / + - || } [+ -] {
    ColumnName
    :HostVariable [[INDICATOR]:IndicatorVariable]
    ?
    :LocalVariable
    :ProcedureParameter
    ::Built-inVariable
    AddMonthsFunction
    AggregateFunction
    Constant
    DateTimeFunction
    CurrentFunction
    LongColumnFunction
    StringFunction
    CASTFunction
    Expression
  }]   [...]

Parameters

+, -

designate unary plus and unary minus. Unary plus assigns the primary a positive value. Unary minus assigns the primary a negative value. Default is positive.

ColumnName

is the name of a column from which a value is to be taken; column names are defined in the "Names" chapter.

USER

The keyword USER can be used as a character constant in several locations as follows:

• In a WHERE clause predicate when comparing it to a character string, for example:

  
     WHERE Owner = USER
     WHERE Owner IN ('ALLUSERS', USER)
  
  

• In the VALUES clause of the INSERT statement, for example:

  
     VALUES (USER)
  
  

• In a DEFAULT clause of a column definition, for example:

  
     Owner CHAR(20) DEFAULT USER NOT NULL
  
  

• In a SELECT list, returning a character string, for example:

  
     SELECT USER, column1
  
  

• In an UPDATE SET clause, assigning a value to a character string, for example:

  
     SET Owner = USER
  
  

USER evaluates to the current DBEUserID. In ISQL, it evaluates to the login name of the ISQL user. From an application program, it evaluates to the login name running the program. USER behaves like a CHAR(20) constant, with trailing blanks if the login name has fewer than 20 characters.

Note that if a column in your table is named USER, it must be preceded with the table name for column values to be selected. The function USER takes precedence over any column named USER.

HostVariable

contains a value in an application program being input to the expression.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0

the value is not NULL

< 0

the value is NULL (The value in the host variable will be ignored.)

?

is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time.

LocalVariable

contains a value in a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

Built-inVariable

is one of the following built-in variables used for error handling:

• ::sqlcode

• ::sqlerrd2

• ::sqlwarn0

• ::sqlwarn1

• ::sqlwarn2

• ::sqlwarn6

• ::activexact

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed for all host languages. However, in application programs, sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used in C. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to Chapter 4 "Constraints, Procedures, and Rules"

AddMonthsFunction

returns a value that represents a DATE or DATETIME value with a certain number of months added to it.

AggregateFunction

is a computed value; aggregate functions are defined in this chapter.

Constant

is a specific value; constants are defined in this chapter.

DateTimeFunction

returns a value that is a conversion of a date/time data type into an INTEGER or CHAR value, or from a CHAR value.

CurrentFunction

returns a value that represents the current DATE, TIME, or DATETIME.

LongColumnFunction

returns information from a long column descriptor.

StringFunction

returns a partial value or attribute of string data.

CASTFunction

converts data from one data type to another.

(Expression)

is one or more of the above primaries, enclosed in parentheses.

*

multiplies two primaries.

/

divides two primaries.

+

adds two primaries.

-

subtracts two primaries.

||

concatenates two string operands.

TIDFunction

returns the database address of a row (or rows for a BULK SELECT) of a table or an updatable view. You cannot use mathematical operators with this function except to compare it (using = or <>) to a value, host variable, or dynamic parameter.

Description

• Arithmetic operators can be used between numeric values, that is, those with data types of FLOAT, REAL, INTEGER, SMALLINT, or DECIMAL. Refer to the "Data Types" chapter for rules governing the resulting precision and scale of DECIMAL operations.

• Arithmetic operators can also be used between DATE, TIME, DATETIME, and INTERVAL values. Refer to the "Data Types" chapter for rules on the valid operations and the resulting data types.

• Elements in an expression are evaluated in the following order:

  • Aggregate functions and expressions in parentheses are evaluated first.

  • Unary plusses and minuses are evaluated next.

  • The * and / operations are performed next.

  • The + and - operations are then performed.

• You can enclose expressions in parentheses to control the order of their evaluation. For example:

  
     10 * 2 - 1 = 19, but
     10 * (2-1) = 10
  
  

• TO_INTEGER is the only date/time function that can be used in arithmetic expressions.

• When two primaries have the same data type, the result is of that data type. For example, when an INTEGER is divided by an INTEGER, the result is INTEGER. In such cases, the result will be truncated.

• If either arithmetic operand is the NULL value, then the result is the NULL value.

• Arithmetic operators cannot be used to concatenate string values. Use || to concatenate string operands.

• Both operands of concatenation operator should be one of the following: CHAR (or VARCHAR, or Native CHAR, or Native VARCHAR), BINARY (or VARBINARY), but no mix of CHAR and BINARY.

• If either concatenation operand is the NULL value, then the result of the concatenation is the NULL value.

• If one concatenation operand is a variable length string (VARCHAR, Native VARCHAR, VARBINARY), then the result data type of the concatenation is a variable length string.

• If both concatenation operands are fixed length string data type (CHAR, Native CHAR, BINARY), then the result of the concatenation is fixed length string.

• The concatenation result will consist of the first operand followed by the second operand. The trailing blanks of the string value are preserved by concatenation regardless of the string's data types. The resultant string may be truncated on the right, if the length exceeds the maximum string length of 3996 bytes. If truncation occurs, a truncation warning is sent.

• Type conversion, truncation, underflow, or overflow can occur when some expressions are evaluated. For more information, refer to the chapter, "Data Types."

• If the value of an indicator variable is less than zero, the value of the corresponding host variable is considered to be NULL.

  NOTE: To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a -1 to indicate a NULL value. However, ALLBASE/SQL interprets all negative indicator variable values as indicating a NULL value in the corresponding host variable.

• The following expressions can evaluate to NULL:

  • Host variable with an indicator variable

  • Local variable

  • Procedure parameter

  • Column

  • Add Months function

  • DateTime function

  • Aggregate function

  • CAST function

  • String function

• A NULL value in an expression causes comparison operators and other predicates to evaluate to unknown. Refer to Chapter 9 "Search Conditions" for more information on evaluation of comparison operators and predicates containing NULL values.

• The ? can be used as a host variable or dynamic parameter in an expression as shown in the following examples:

  • In the WHERE clause of any SELECT statement:

    
       SELECT *
         FROM PurchDB.Orders
        WHERE PartNumber = ?
              AND OrderDate > ?
       ORDER BY OrderDate
    
    

  • In the WHERE and SET clauses of an UPDATE statement:

    
       UPDATE PurchDB.Parts
          SET SalesPrice = ?
        WHERE PartNumber = ?
    
    

  • In the WHERE clause of a DELETE statement:

    
       DELETE FROM PurchDB.OrderItems
            WHERE ItemDueDate
           BETWEEN ? and ?
    
    

  • In the VALUES clause of an INSERT or a BULK INSERT statement. In this example each ? corresponds in sequential order to a column in the PurchDB.OrderItems table:

    
      BULK INSERT INTO PurchDB.OrderItems VALUES (?,?,?,?)
    
    

    See the syntax descriptions for each DML statement, and for the PREPARE, DESCRIBE, EXECUTE, and OPEN statements for details of dynamic parameter usage.

Example

  CREATE TABLE PurchDB.SupplyPrice
         (Part Number     CHAR(16) NOT CASE SENSITVE not null unique,
          VendorNumber    INTEGER
          VendPartNumber  CHAR(16) lang=german,
          UnitPrice       DECIMAL (10,2),
          Delivery Days   SMALLINT,
          DiscountQty     SMALLINT)

  SELECT PartNumber || VendPartNumber, UnitPrice
    from PurchDB.SupplyPrice;