Data type and expression segments

Data type and expression segments can appear in SQL statements.

Data type and expression segments can include the following items:
  • Data Type
  • DATETIME Field Qualifier
  • INTERVAL Field Qualifier
  • Expression
  • Aggregate Expression
  • AVG, COUNT, MAX, MIN, SUM, RANGE, STDEV, VARIANCE, and User-Defined Aggregates
  • Arithmetic Expressions
  • Binary ( +, -, *, / ) Operators, Operator Functions, and Unary ( +, - ) Operators
  • Cast Expressions
  • CAST function and Cast ( :: ) Operator
  • Collection Subquery
  • Column Expressions
  • Column Name, ROWID, and Substring ( [ ... ] ) Operator
  • CONCAT Function and Concatenation ( || ) Operator
  • Condition Segment and Conditional Expressions
  • Comparison Condition: AND, OR, NOT, BETWEEN, IS NULL, LIKE, MATCHES, and Relational Operators
  • Condition with Subquery: IN, EXISTS, ALL, ANY, and SOME Operators
  • Boolean UDF
  • CASE Expressions
  • NVL Function
  • DECODE Function
  • Constant Expressions: CURRENT, SYSDATE, TODAY, DBSERVERNAME, SITENAME, UNITS, CURRENT_USER, and USER
  • Literal Value
  • Literal Collection
  • Literal DATETIME
  • Literal INTERVAL
  • Literal Number
  • Literal Row
  • Quoted String
  • Constructor Expressions
  • Collection Constructor
  • ROW Constructor Function Expressions
  • Algebraic Functions: ABS, MOD, POW, power ROOT, ROUND, SQRT, and TRUNC Functions
  • CARDINALITY Function
  • DBINFO Function
  • Encryption and Decryption Functions: DECRYPT_BINARY, DECRYPT_CHAR, ENCRYPT_AES, ENCRYPT_TDES, and GETHINT Functions
  • Exponential and Logarithmic Functions: EXP, LOGN, and LOG10 Functions
  • HEX Function
  • Hierarchical Query Operators and Functions: CONNECT_BY_ROOT, PRIOR, and SQL_CONNECT_BY_PATH
  • IFX_ALLOW_NEWLINE Function
  • Length Functions: CHARACTER_LENGTH, CHAR_LENGTH, LENGTH, and OCTET_LENGTH Functions
  • Sequence Operators: CURRVAL, NEXTVAL
  • Smart Large Object Functions: FILETOBLOB, FILETOCLOB, LOCOPY, and LOTOFILE Functions
  • String-Manipulation Functions: LPAD, RPAD, TRIM, REPLACE, SUBSTR, SUBSTRING, INITCAP, LOWER, and UPPER Functions
  • Time Functions: DATE, DAY, EXTEND, MDY, MONTH, TO_CHAR, TO_DATE, WEEKDAY, and YEAR Functions
  • Trigger-Type Boolean Operators: DELETING, INSERTING, SELECTING, and UPDATING
  • Trigonometric Functions: ACOS, ASIN, ATAN, ATAN2, COS, SIN, and TAN Functions
  • User-Defined Functions
  • Statement-Local Variable Expressions

You can also use host variables or SPL variables as expressions. For an alphabetic list of expressions with page references, see List of Expressions.