A value expression is a symbol or string of symbols used to
represent or calculate a single value. When you use a value
expression in a statement, SQL retrieves or calculates the value
associated with the expression and uses that value when executing
the statement.
Value expressions are also called scalar expressions or
expressions.
There are several different types of value expressions:
o A literal directly specifies a value. See the Literals HELP
topic for more information.
o A parameter represents a value in a host language program or
in an SQL module. See the User_Supplied_Names HELP topic for
more information.
o A column name represents a value contained in table rows. See
the User_Supplied_Names HELP topic for details on specifying
value expressions with column names.
o A column select expression used as a value expression
specifies a one-value result table. See the Column_Select_
Expressions HELP topic for more information.
o A built-in function calculates values based on input value
expressions. See the Built_In_Functions HELP topic for
details.
SQL built-in functions include functions such as CAST,
CURRENT_USER, and TRIM. For a complete list of built-in
functions, see Built In Functions.
o An aggregate function calculates a single value for a
collection of rows in a result table. See the Aggregate_
Functions HELP topic for details.
SQL aggregate functions are:
- AVG
- COUNT
- MAX
- MIN
- STDDEV, STDDEV_SAMP, STDDEV_POP
- SUM
- VARIANCE, VAR_SAMP, VAR_POP
o SQL functions (CONCAT, CONCAT_WS, CONVERT, DECODE, GREATEST,
LEAST, LENGTH, LENGTHB, SIGN, SYSDATE, SYSTIMESTAMP, SYS_
GUID, ROUND and TRUNC) have been added to the Oracle Rdb SQL
interface for convergence with Oracle SQL. See the Oracle
Server SQL Language Reference Manual for more information.
o The DBKEY or ROWID keyword represents the value of an internal
pointer called a database key to a table row. The ROWID
keyword is a synonym to the DBKEY keyword. See the DBKEY HELP
topic for more information.
o A character value expression represents a value that belongs
to the CHAR, CHARACTER, VARCHAR, LONG VARCHAR, NCHAR, or
NCHAR VARYING data type. You can link two character value
expressions together using the concatenation operator (||).
o You can also combine certain value expressions with arithmetic
operators to form a value expression.
o A substring specifies a portion of a character value
expression that you can manipulate using arithmetic operators.
o A conditional expression is a form of the value expression
that allows applications to return alternative information
within an expression. See the Conditional_Expressions HELP
topic for details.
Conditional expressions are:
- ABS
- CASE
- COALESCE (or NVL)
- DECODE
- GREATEST
- LEAST
- NULLIF
- NVL2
- SIGN
The following syntax diagrams show the format of an SQL value
expression:
value-expr =
---+--> numeric-value-expr -----+----->
+--> char-value-expr --------+
+--> date-time-value-expr ---+
+--> interval-value-expr ----+
+--> date-vms-value-expr ----+
+--> DBKEY ------------------+
+--> NULL -------------------+
+--> ROWID ------------------+
numeric-value-expr =
--+-+------+-> numeric-value-factor --+----------->
| +-> + -+ +-> + -+-+
| +-> - -+ +-> - -+ |
| +-> * -+ |
| +-> / -+ |
+----------------------- <-------------------+
numeric-value-factor =
--+---> common-value-expr ------------------------------------+-->
+---> numeric-literal --------------------------------------+
+---> BITSTRING (numeric-value-expr FROM -----+ |
| +---------------------------------------+ |
| +-> <start-position> -+------------------------+- ) --+
| +-> FOR <string-length> -+ |
+---> uid-numeric-functions --------------------------------+
+---> length-value-functions -------------------------------+
+---> POSITION --> ( --> char-value-expr IN ----+ |
| +--------------------------------------------+ |
| +-> char-value-expr --+---------------------------+-> ) -+
| +> FROM numeric-value-expr -+ |
+---> EXTRACT --> ( date-time-field --+ |
+--------------------------------+ |
+---> FROM --> date-time-value-expr ) -----------------+
common-value-expr =
-+-> <column-name> -------------------------------------------+->
+-> <parameter> ---------------------------------------------+
+-> <qualified-parameter> -----------------------------------+
+-> <variable> ----------------------------------------------+
+-> (col-select-expr) ---------------------------------------+
+-> CAST --> ( --> value-expr -> AS +-> data-type -----+> ) -+
| +-> <domain-name> -+ |
+-> VALUE ---------------------------------------------------+
+-> aggregate-function --------------------------------------+
+-> conditional-expr ----------------------------------------+
+-> function-invocation -------------------------------------+
+-> (value-expr) --------------------------------------------+
aggregate-function =
-+-> COUNT (*) -------------------------------------------+-+----------------+>
+-> COUNT ----+->( -+--------------+-> value-expr -> ) --+ +> filter-clause +
+-> AVG ------+ +-> ALL -------+ |
+-> MIN ------+ +-> DISTINCT --+ |
+-> MAX ------+ |
+-> SUM ------+ |
+-> STDDEV ---+ |
+-> VARIANCE -+ |
+-> STDDEV_POP --+-->( -> numeric-value-expr --> ) ------+
+-> STDDEV_SAMP -+
+-> VAR_POP ----+
+-> VAR_SAMP ---+
filter-clause =
-+---------------------------------------+->
+-> ( --> WHERE --> predicate --> ) --+
conditional-expr =
-+-> NULLIF --> ( --> value-expr --> , --> value-expr --> ) ----------+->
+-> COALESCE -+-> ( -> value-expr -+-> , --> value-expr +-> ) --------+
+-> NVL ------+ +--------- <---------+ |
+-> GREATEST -+ |
+-> LEAST ----+ |
+-> NVL2 (value-expr, value-expr, value-expr) ------------------------+
+-> DECODE (value-expr, -+> search , result +--+--------------+--> ) -+
| +------- , <-------+ +-> , default -+ |
+-> ABS --+-> (value-expr) -------------------------------------------+
+-> SIGN -+ |
+-> simple-case-expr -------------------------------------------------+
+-> searched-case-expr -----------------------------------------------+
simple-case-expr =
-> CASE -> value-expr +> WHEN with-values ---> THEN -> value-expr -++
+---------------------- <--------------------+|
+------------------------------------- <----------------------------+
+-+-------------------------+-----> END ---------------------------->
+-> ELSE -->value-expr ---+
searched-case-expr =
--> CASE +-> WHEN predicate --> THEN ---> value-expr -+-+
+---------------------- <---------------------+ |
+--------------------------------------------------------+
+--+-----------------------------+--> END ---------------->
+-> ELSE ---> value-expr -----+
with-values =
--+--+--------------------------+-> value-expr --+--+---->
| +--> partial_predicate1 ---+ | |
| +--> partial_predicate2 --------------------+ |
+------------ , <---------------------------------+
partial_predicate1 =
-+---> NOT CONTAINING ----------+-->
+---> NOT STARTING WITH -------+
+---> NOT MATCHING ------------+
+---> = -----------------------+
+---> <> ----------------------+
+---> ^= ----------------------+
+---> != ----------------------+
+---> < -----------------------+
+---> >= ----------------------+
+---> > -----------------------+
+---> <= ----------------------+
partial_predicate2 =
-+---> IS NOT NULL -------------------------------------------------+->
+---> NOT BETWEEN -+----------------+- value-expr AND value-expr --+
| +-> ASYMMETRIC --+ |
| +-> SYMMETRIC ---+ |
+---> NOT LIKE --> <pattern> --+-------------------------------+---+
| +-> ESCAPE <escape-character> --+ |
| +-> IGNORE CASE ----------------+ |
+---> NOT IN --+-> value-expr --------------------+----------------+
+-> ( -++-> value-expr --++--> ) --+
|+-> select-expr -+|
+---- , <----------+
function-invocation =
--> <function-name> --> ( -+-----------------+-> ) -->
++-> value-expr -++
+-> DEFAULT ----+
+------ , <-----+
uid-numeric-functions =
-+---> UID -----------------------+-->
+---> CURRENT_UID ---------------+
+---> SESSION_UID ---------------+
+---> SYSTEM_UID ----------------+
length-value-functions =
-+---> CHARACTER_LENGTH -+-> (char-value-expr) --------------+--->
+---> CHAR_LENGTH ------+ |
+---> LENGTH -----------+ |
+---> OCTET_LENGTH -----+-> (value-expr) -------------------+
+---> LENGTHB ----------+ |
+---> SIZEOF -----------+-> (value-expr) -------------------+
+---> VSIZE ------------+
char-value-expr =
+-> common-value-expr -------------------------------------------------------+->
+-> <string-literal> --------------------------------------------------------+
+-> user-string-functions ---------------------------------------------------+
+-> SYS_GUID ( ) ------------------------------------------------------------+
+-> UPPER (char-value-expr) -------------------------------------------------+
+-> LOWER (char-value-expr) -------------------------------------------------+
+-> TRANSLATE (char-value-expr -+->USING <translation-name>) ----------------+
| +-> , char-value-expr , char-value-expr) -+
+-> CONCAT ----+-> (value-expr -+-> , value-expr -+-> ) --------------------+
+-> CONCAT_WS -+ +-------<---------+ |
+-> trim-expr ---------------------------------------------------------------+
+-> SUBSTRING ( char-value-expr FROM --+ |
| +------------------------------------+ |
| +-> <start-position> +--------------------------+-- ) ---------------------+
| +-> FOR <string-length> --+ |
+-> char-value-expr --> || --> char-value-expr ------------------------------+
user-string-functions =
-+---> USER ----------------------+-->
+---> CURRENT_USER --------------+
+---> SESSION_USER --------------+
+---> SYSTEM_USER ---------------+
trim-expr =
---> TRIM --+
+-----------+
+-> ( -+-------------------------------------------+-+
++------------++-------------------+-> FROM + |
+-> BOTH ----++-> char-value-expr + |
+-> LEADING -+ |
+-> TRAILING + |
+----------------------------------------------------+
+--> char-value-expr --> ) ---------------------------->
date-time-value-expr =
-+-> date-time-value-expr -+-> + -+-> interval-value-expr -+->
| +-> - -+ |
+-> interval-value-expr --> + --> date-time-value-expr ---+
+-> date-time-value-factor -------------------------------+
date-time-value-factor =
--+---> common-value-expr -------------------------------+->
+---> date-time-literal -------------------------------+
+---> CURRENT_DATE ------------------------------------+
+---> SYSDATE -----------------------------------------+
+---> SYSTIMESTAMP ------+-+-------------------------+-+
+---> LOCALTIME ---------+ +-> (seconds-precision) --+
+---> CURRENT_TIME ------+
+---> LOCALTIMESTAMP ----+
+---> CURRENT_TIMESTAMP -+
date-time-literal =
--+-> TIME --> ' --> time-body --> ' ------------------------+-->
+-> DATE -+---------+-> ' --> date-body --> ' -------------+
| +-> ANSI -+ |
+-> TIMESTAMP --> ' --+-> date-body time-body -+--> ' --+
+-> date-body : time-body -+
time-body =
---> <hours> : <minutes> : <seconds> ---+
+---------------------<---------------+
++---------------------------+--------->
+-> . <fractional-seconds> -+
date-body =
---> <year> - <month> - <day> -->
interval-value-expr =
-++----++-> interval-value-factor -+----------------------------++->
|+> + +| +> * +-> numeric-value-expr -+|
|+> - +| +> / + ||
| +-> numeric-value-expr --> * -> interval-value-factor --+|
+-----------------------------+- + <--+-------------------------+
+- - <--+
interval-value-factor =
-+-> interval-literal ---+------------------------------------------+>
+-> common-value-expr --+ |
+-> ( -> date-time-value-expr -> - -> date-time-value-expr -> ) -+ |
+-----------------------------------------------------------+ |
+-> interval-qualifier ---------------------------------------+
date-vms-value-expr =
--+-+-------------+-> ' --> <date-vms-string> --> ' +->
| +-> DATE VMS -+ |
+---> common-value-expr --------------------------+