3 SQL Expressions

Syntax for SQL Expression Types

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the datatype of its components.

Expressions have several forms. The sections that follow show the syntax for each form of expression. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.

CASE expression

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

Compound expression

{ (expr)
| { + | - | PRIOR } expr
| expr { * | / | + | - | || } expr
}
Note: The double vertical bars are part of the syntax
         (indicating concatenation) rather than BNF notation.

CURSOR expression

CURSOR (subquery)

DATETIME expression

datetime_value_expr AT
   { LOCAL
   | TIME ZONE { ' [ + | - ] hh:mm'
               | DBTIMEZONE
               | 'time_zone_name'
               | expr
               }
   }

Function expression

any built-in SQL function or user-defined function can be used as an expression

INTERVAL expression

interval_value_expr
   { DAY [ (leading_field_precision) ] TO
     SECOND [ (fractional_second_precision) ]
   | YEAR [ (leading_field_precision) ] TO
     MONTH
   }

Model expression

{ measure_column [ { condition | expr }[ , { condition | expr } ...] ]
| aggregate_function 
     { [ { condition | expr }[ , { condition | expr } ...] ]
     | [ single_column_for_loop [, single_column_for_loop] ... ]
     | [ multi_column_for_loop ]
     }
| analytic_function
}

Object access expression

{ table_alias.column.
| object_table_alias.
| (expr).
}
{ attribute [.attribute ]...
  [.method ([ argument [, argument ]... ]) ]
| method ([ argument [, argument ]... ])
}

Scalar subquery expression

a subquery that returns exactly one column value from one row can be used as an expression

Simple expression

{ [ query_name.
  | [schema.]
    { table. | view. | materialized view. }
  ] { column | ROWID }
| ROWNUM
| string
| number
| sequence. { CURRVAL | NEXTVAL }
| NULL
}

Type constructor expression

[ NEW ] [ schema. ]type_name
   ([ expr [, expr ]... ])

Variable expression

:host_variable
   [ [ INDICATOR ]
     :indicator_variable
   ]