7.21.1 Specification format and rules for value expressions

(1) Specification format

value-expression ::= {numeric-expression|character-value-expression|datetime-value-expression|binary-value-expression}
 
 
  numeric-expression ::= {value-expression-primary|arithmetic-operation}
  character-value-expression ::= {value-expression-primary|concatenation-operation}
  datetime-value-expression ::= {value-expression-primary|datetime-operation}
  binary-value-expression ::= {value-expression-primary|concatenation-operation}
 
    value-expression-primary ::= {(value-expression)|column-specification|value-specification|set-function
                    |scalar-function|window-function|CASE-expression
                    |labeled-duration|scalar-subquery
                    |array-element-reference|field-reference}

To Page Top

(2) Explanation of specification format

arithmetic-operation:

For details about arithmetic operations, see 7.26 Arithmetic operations.

concatenation-operation:

For details about concatenation operations, see 7.27 Concatenation operations.

datetime-operation:

For details about datetime operations, see 7.28 Datetime operations.

column-specification:

For details about column specifications, see (5) Column specification format in 6.1.5 Qualifying a name.

value-specification:

For details about value specifications, see 7.22 Value specification.

set-function:

For details about set functions, see 7.23 Set functions.

scalar-function:

For details about scalar functions, see 8. Scalar Functions.

window-function:

For details about window functions, see 7.24 Window functions.

CASE-expression:

For details about CASE expressions, see 7.30 CASE expression.

labeled-duration:

For details about labeled durations, see 7.29 Labeled duration.

scalar-subquery:

For details about scalar subqueries, see 7.3 Subqueries.

array-element-reference:

For details about array element references, see 7.31 Array element reference.

field-reference

For details about field references, see 7.32 Field reference.

To Page Top

(3) Rules

1. A maximum of 500 total scalar operations and set functions can be specified in a value expression. If a column specified in a value expression is a column from a viewed table, derived table, or query name, the total number of value expressions after expanding the value expression it is based on cannot exceed 10,000.

   Note that in certain circumstances, depending on how a viewed table, derived table, or query name is specified, a value expression might be added to it. For the circumstances under which the value expression is added, see 7.33.6 When the scalar function CONVERT is added to an internal derived table.

   Scalar operation is a general term for the following operations that can be specified in a value expression:

   • Arithmetic operation

   • Concatenation operation

   • Datetime operation

   • Scalar function

   • Window function

   • CASE expression

   • Array element reference

   • Field reference

2. When the scalar operations listed below are nested, the upper limit on nesting is 15 levels. If a column specified in a value expression is a column from a viewed table or derived table, after expanding the value expression they are based on, make sure that the nesting depth of the scalar operations does not exceeded 15 levels.

   Note that in certain circumstances, depending on how a viewed table or derived table is specified, a value expression might be added to it. For the circumstances under which the value expression is added, see 7.33.6 When the scalar function CONVERT is added to an internal derived table.

   Even if different scalar operations are combined, the upper limit on nesting remains a total of 15 levels.

   • Scalar function

   • Window function

   • CASE expression

   The examples below illustrate how nesting levels are counted.

   Example 1: The scalar function SUBSTR nested 15 times

   SUBSTR(SUBSTR(SUBSTR(SUBSTR(SUBSTR(
   SUBSTR(SUBSTR(SUBSTR(SUBSTR(SUBSTR(
   SUBSTR(SUBSTR(SUBSTR(SUBSTR(SUBSTR(
   SUBSTR("C1",1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1)

   Example 2: CASE expressions within CASE expressions, nested two levels deep

   CASE WHEN
       CASE WHEN
                   CASE WHEN "C1">100
                       THEN  "C1"-100
                       ELSE "C1"
                   END >100
               THEN "C1"-100
               ELSE "C1"
           END >100
       THEN "C1"-100
       ELSE "C1"
   END

   Example 3: A mixture of a CASE expression and the scalar function SUBSTR, with a maximum nesting level of 2

   SUBSTR(CASE WHEN "C1">100 THEN SUBSTR("C2",1,10)
                             ELSE SUBSTR("C2",1,5)
          END,1,5)

3. If an overflow occurs during any of these operations, an SQL error is generated.

4. The order in which scalar operations are evaluated obeys the following priority hierarchy:

   • Items in parentheses

   • * or /

   • +, -, or ||

5. The table below shows the conditions under which value expressions are equivalent to literals. However, note that the data type and data length of the result of the value expression will be the data type and data length derived from each component value expression rather than the data type and data length of the literal.

   Table 7‒18: Conditions under which value expressions are equivalent to literals

   No.	Type of value expression		Conditions under which the value expression is equivalent to a literal
   1	Arithmetic operation		When you specify literals for the first and second operands
   2	Concatenation operation
   3	Datetime operation		When all of the following conditions are satisfied: A literal is specified for the first operand. The second operand is a labeled duration, and a literal is specified for the value-expression-primary specified in the labeled duration. Literals are specified for the value-expression-primary instances that are multiplied or divided (only when multiplying or dividing labeled durations)
   4	Scalar functions	ABS	When you specify a literal for the target data
   5		ACOS
   6		ARRAY_MAX_CARDINALITY	Always treated as a literal.
   7		ASCII	When you specify a literal for the target data, except in the following case: When you specify data whose actual length is 0 bytes or 0 characters for the target data
   8		ASIN	When you specify a literal for the target data
   9		ATAN
   10		ATAN2	When you specify literals for both target data items
   11		BIN	When you specify a literal for the target data
   12		BITAND	When you specify literals for both target data items
   13		BITLSHIFT	When you specify literals for the target data and the number of bits to shift
   14		BITNOT	When you specify a literal for the target data
   15		BITOR	When you specify literals for both target data items
   16		BITRSHIFT	When you specify literals for the target data and the number of bits to shift
   17		BITXOR	When you specify literals for both target data items
   18		CAST	When you specify a literal for the conversion target data, except in the following case: When you specify conversion of a character string literal whose actual length is 0 bytes into something other than character string data
   19		CEIL	When you specify a literal for the target data
   20		CHR	When you specify a literal for the target data, except in the following case: When you specify a negative integer value for the target data
   21		COALESCE	When there is target data specified for at least one argument, and you specify literals for all the target data
   22		CONCAT	When you specify literals for both target data items
   23		CONVERT	When you specify a literal for the conversion target data, except in the following cases: When you specify conversion of a character string literal whose actual length is 0 bytes into something other than character string data When you specify the format specification
   24		COS	When you specify a literal for the target data
   25		COSH
   26		DATEDIFF	When you specify literals for the start date and end date
   27		DAYOFWEEK	When you specify a literal for the target data
   28		DAYOFYEAR
   29		DEGREES	When you specify a literal for the angle
   30		EXP	When you specify a literal for the exponent
   31		EXTRACT	When you specify a literal for the source data
   32		FLOOR	When you specify a literal for the target data
   33		GETAGE	When you specify literals for the date of birth and reference date
   34		HEX	When you specify a literal for the target data
   35		LASTDAY	When you specify a literal for the date data
   36		LEFT	When you specify literals for the source character string data and extraction length, except in the following case: When you specify a negative value for the extraction length
   37		LENGTH	When you specify a literal for the target data
   38		LENGTHB
   39		LN
   40		LOG	When you specify literals for the base and target data
   41		LOWER	When you specify a literal for the character string data to be converted
   42		LPAD	When you specify literals for the target data, number of characters, and padding character string, except in the following case: When you specify a negative value for the number of characters
   43		LTRIM	When you specify literals for the target data and the characters to be removed
   44		MOD	When you specify literals for the dividend and divisor
   45		PI	Always treated as a literal.
   46		POWER	When you specify literals for the target data and exponent
   47		RADIANS	When you specify a literal for the angle
   48		REPLACE	When either of the following conditions is met: When you specify literals for the target data, character string to be replaced, and replacement character string When you specify literals for the target data and character string to be replaced, and omit the replacement character string
   49		RIGHT	When you specify literals for the source character string data and extraction length, except in the following case: When you specify a negative value for the extraction length
   50		ROUND	Mathematical function ROUND: When you specify literals for the target data and number of digits Datetime function ROUND: When you specify a literal for the datetime data
   51		RPAD	When you specify literals for the target data, number of characters, and padding character string, except in the following case: When you specify a negative value for the number of characters
   52		RTRIM	When you specify literals for the target data and the characters to be removed
   53		SIGN	When you specify a literal for the target data
   54		SIN
   55		SINH
   56		SQRT
   57		SUBSTR	When you specify literals for the source character string data, start position, and extraction length, except in the following case: When you specify a negative value for the extraction length
   58		SUBSTRB	When you specify literals for the source binary data, start position, and number of bytes to extract (except in the following case) When you specify a negative value for the number of bytes to extract
   59		TAN	When you specify a literal for the target data
   60		TANH
   61		TIMESTAMPADD	When you specify literals for the add value target data
   62		TIMESTAMPDIFF	When you specify literals for the start date and end date
   63		TRANSLATE	When you specify literals for the target data, characters to replace, and replacement characters
   64		TRIM	When you specify literals for the target data and the characters to be removed
   65		TRUNC	Mathematical function TRUNC: When you specify literals for the target data and number of digits Datetime function TRUNC: When you specify a literal for the datetime data
   66		UPPER	When you specify a literal for the character string data to be converted

To Page Top