7.20.1 Specification format and rules for value expressions

(1) Specification format

value-expression ::= {numeric-value-expression | character-value-expression | datetime-value-expression | binary-value-expression}
 
 
  numeric-value-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}

To Page Top

(2) Explanation of specification format

arithmetic-operation:

For details about arithmetic operations, see 7.25 Arithmetic operations.

concatenation-operation:

For details about concatenation operations, see 7.26 Concatenation operations.

datetime-operation:

For details about datetime operations, see 7.27 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.21 Value specification.

set-function:

For details about set functions, see 7.22 Set functions.

scalar-function:

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

window-function:

For details about window functions, see 7.23 Window functions.

CASE-expression:

For details about CASE expressions, see 7.29 CASE expression.

labeled-duration:

For details about labeled durations, see 7.28 Labeled duration.

scalar-subquery:

For details about scalar subqueries, see 7.3 Subqueries.

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.30.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

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.30.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‒12: 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		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
   7		ASIN	When you specify a literal for the target data
   8		ATAN
   9		ATAN2	When you specify literals for both target data items
   10		BIN	When you specify a literal for the target data
   11		BITAND	When you specify literals for both target data items
   12		BITLSHIFT	When you specify literals for the target data and the number of bits to shift
   13		BITNOT	When you specify a literal for the target data
   14		BITOR	When you specify literals for both target data items
   15		BITRSHIFT	When you specify literals for the target data and the number of bits to shift
   16		BITXOR	When you specify literals for both target data items
   17		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
   18		CEIL	When you specify a literal for the target data
   19		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
   20		COALESCE	When there is target data specified for at least one argument, and you specify literals for all the target data
   21		CONCAT	When you specify literals for both target data items
   22		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
   23		COS	When you specify a literal for the target data
   24		COSH
   25		DATEDIFF	When you specify literals for the start date and end date
   26		DAYOFWEEK	When you specify a literal for the target data
   27		DAYOFYEAR
   28		DEGREES	When you specify a literal for the angle
   29		EXP	When you specify a literal for the exponent
   30		EXTRACT	When you specify a literal for the source data
   31		FLOOR	When you specify a literal for the target data
   32		GETAGE	When you specify literals for the date of birth and reference date
   33		HEX	When you specify a literal for the target data
   34		LASTDAY	When you specify a literal for the date data
   35		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
   36		LENGTH	When you specify a literal for the target data
   37		LENGTHB
   38		LN
   39		LOG	When you specify literals for the base and target data
   40		LOWER	When you specify a literal for the character string data to be converted
   41		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
   42		LTRIM	When you specify literals for the target data and the characters to be removed
   43		MOD	When you specify literals for the dividend and divisor
   44		PI	Always treated as a literal.
   45		POWER	When you specify literals for the target data and exponent
   46		RADIANS	When you specify a literal for the angle
   47		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
   48		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
   49		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
   50		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
   51		RTRIM	When you specify literals for the target data and the characters to be removed
   52		SIGN	When you specify a literal for the target data
   53		SIN
   54		SINH
   55		SQRT
   56		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
   57		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
   58		TAN	When you specify a literal for the target data
   59		TANH
   60		TRANSLATE	When you specify literals for the target data, characters to replace, and replacement characters
   61		TRIM	When you specify literals for the target data and the characters to be removed
   62		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
   63		UPPER	When you specify a literal for the character string data to be converted

To Page Top