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 data type 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.

SQLQR497CASE expressions

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

SQLQR498Column expressions

A column expression can be a simple expression, compound expression, function expression, or expression list, containing only columns of the subject table, constants, and deterministic functions.

SQLQR499Compound expressions

{ (expr)
| { + | - | PRIOR } expr
| expr { * | / | + | - | || } expr
}

Note: The double vertical bars are part of the syntax
      (indicating concatenation) rather than BNF notation.

SQLQR500CURSOR expressions

CURSOR (subquery)

SQLQR501Datetime expressions

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

SQLQR502Function expressions

You can use any built-in SQL function or user-defined function as an expression.

SQLQR503Interval expressions

( expr1 - expr2 )
   { DAY [ (leading_field_precision) ] TO
     SECOND [ (fractional_second_precision) ]
   | YEAR [ (leading_field_precision) ] TO
     MONTH
   }

SQLQR504Model expressions

{ 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
}

SQLQR505Object access expressions

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

SQLQR506Placeholder expressions

:host_variable
   [ [ INDICATOR ]
     :indicator_variable
   ]

SQLQR507Scalar subquery expressions

A scalar subquery expression is a subquery that returns exactly one column value from one row.

SQLQR508Simple expressions

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

SQLQR509Type constructor expressions

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