Determining data types of untyped expressions
An untyped expression refers to the usage of a parameter marker which is specified without a target data type associated with it, a null value which is specified without a target data type associated with it, or a DEFAULT keyword.
Untyped expressions can be used in SQL
statements as long as one of the following conditions is true:
- A
PREPARE statement is being executed by a CLI or JDBC application to
compile the SQL statement; the client interface is using deferred
prepare; and the registry variable, DB2_DEFERRED_PREPARE_SEMANTICS
is set to YES. In this case, any untyped parameter marker derives
its data type based on the input descriptor associated with the subsequent
OPEN or EXECUTE statement. The length attribute is set to the maximum
of the length according to the UNTYPED row, as described in the Table 2 in
Functions
and the length as determined from the following tables. For data types not listed as a target type in Table 2 inFunctions
, the length from the input descriptor associated with the subsequent OPEN or EXECUTE statement will be used. The data types and lengths may be modified depending on the usage of the untyped parameter marker in the SQL statement. - The data type can be determined based on the context in the SQL statement. These locations and the resulting data types are shown in the following table. The locations are grouped into expressions, predicates, built-in functions, and user-defined routines to assist in determining the applicability of an untyped expression. If the data type cannot be determined based on the context, an error is issued.
For some cases not listed, untyped expressions in a select list will be resolved to a data type determined based on the usage in the SQL statement.
The code page of the untyped expression is determined by the context. Where there is no context, the code page is the same as if the untyped expression was cast to a VARCHAR data type.
The tables that follow show character string and graphic string data types in string units associated with a database environment where the string units default is SYSTEM. If the Unicode database environment has the string units set to CODEUNITS32, then any character string or graphic string length attributes that represent the data type maximum length should be considered to represent the data type maximum in CODEUNITS32. All character string or graphic string data types have the default string units of the database environment.
| Untyped Expression Location | Data Type |
|---|---|
| Alone in a select list | If
the untyped expression is unnamed or is named but not subsequently
referenced in the SQL statement, then an error is returned, unless
the untyped expression is the null value. In such cases, the data
type is VARCHAR(1). If the untyped expression is named and subsequently referenced in the SQL statement, then the data type may be determined from the subsequent usage. For more information, refer to the "Determining data type from usage" note that follows this table. |
| Both operands of a single arithmetic operator, after considering
operator precedence and order of operation rules Includes cases
such as:
|
DECFLOAT(34) |
| One operand of a single operator in an arithmetic expression
(not a datetime expression) Includes cases such as:
|
The data type of the other operand |
| Labelled duration within a datetime expression (note that the portion of a labelled duration that indicates the type of units cannot be a parameter marker) | DECIMAL(15,0) |
| Any other operand of a datetime expression (for example, 'timecol + ?' or '? - datecol') | Error |
| Both operands of a CONCAT operator | VARCHAR(254) |
| One operand of a CONCAT operator when the other operand is a non-CLOB character data type | If one operand is either CHAR(n) or VARCHAR(n), where n is less than 128, the other is VARCHAR(254 - n); in all other cases, the data type is VARCHAR(254) |
| One operand of a CONCAT operator when the other operand is a non-BLOB binary data type | If one operand is either BINARY(n) or VARBINARY(n), where n is less than 128, the other is VARBINARY(254 - n); in all other cases, the data type is VARBINARY(254) |
| One operand of a CONCAT operator, when the other operand is a non-DBCLOB graphic data type | If one operand is either GRAPHIC(n) or VARGRAPHIC(n), where n is less than 64, the other is VARGRAPHIC(127 - n); in all other cases, the data type is VARGRAPHIC(127) |
| One operand of a CONCAT operator, when the other operand is a large object string | Same as that of the other operand |
| The expression following the CASE keyword in a simple CASE expression | Result of applying the Rules for the result data typesto the expressions following the WHEN keyword that are other than untyped expressions |
| At least one of the result-expressions in a CASE expression (both simple and searched), with the rest of the result-expressions being untyped expressions | Error |
| Any or all expressions following the WHEN keyword in a simple CASE expression | Result of applying the Rules for result data typesto the expression following CASE and the expressions following WHEN keyword that are other than an untyped expression |
| A result-expression in a CASE expression (both simple and searched), when at least one result-expression is not an untyped expression | Result of applying the Rules for result data typesto all result-expressions that are other than an untyped expression |
| Alone as a column-expression in a single-row VALUES clause that is not within an INSERT statement and not within the VALUES clause of in insert operation of a MERGE statement | Error if the untyped expression is unnamed or is named but not subsequently referenced in the SQL statement. If the untyped expression is named and subsequently referenced in the SQL statement, then the data type may be determined from the subsequent usage. For more information, refer to the "Determining data type from usage" note that follows this table. |
| Alone as a column-expression in a multi-row VALUES clause that is not within an INSERT statement, and for which the column-expressions in the same position in all other row-expressions are untyped expressions | Error if the untyped expression is unnamed or is named but not subsequently referenced in the SQL statement. If the untyped expression is named and subsequently referenced in the SQL statement, then the data type may be determined from the subsequent usage. For more information, refer to the "Determining data type from usage" note that follows this table. |
| Alone as a column-expression in a multi-row VALUES clause that is not within an INSERT statement, and for which the expression in the same position of at least one other row-expression is not an untyped expression | Result of applying the Rules for result data typeson all operands that are other than untyped expressions |
| Alone as a column-expression in a single-row VALUES clause within an INSERT statement | The data type of the column. If the column is defined as a user-defined distinct type, it is the source data type of the user-defined distinct type. If the column is defined as a user-defined structured type, it is the structured type, also indicating the return type of the transform function. |
| Alone as a column-expression in a multi-row VALUES clause within an INSERT statement | The data type of the column. If the column is defined as a user-defined distinct type, it is the source data type of the user-defined distinct type. If the column is defined as a user-defined structured type, it is the structured type, also indicating the return type of the transform function. |
| Alone as a column-expression in a values-clause of the source table for a MERGE statement | Error if the untyped expression is unnamed or is named but not subsequently referenced in the SQL statement. If the untyped expression is named and subsequently referenced in the SQL statement, then the data type may be determined from the subsequent usage. For more information, refer to the "Determining data type from usage" note that follows this table. |
| Alone as a column-expression in the VALUES clause of an insert operation of a MERGE statement | The data type of the column. If the column is defined as a user-defined distinct type, it is the source data type of the user-defined distinct type. If the column is defined as a user-defined structured type, it is the structured type, also indicating the return type of the transform function. |
| Alone as a column-expression on the right side of assignment-clause for an update operation of a MERGE statement | The data type of the column. If the column is defined as a user-defined distinct type, it is the source data type of the user-defined distinct type. If the column is defined as a user-defined structured type, it is the structured type, also indicating the return type of the transform function. |
| Alone as a column-expression on the right side of a SET clause in an UPDATE statement | The data type of the column. If the column is defined as a user-defined distinct type, it is the source data type of the user-defined distinct type. If the column is defined as a user-defined structured type, it is the structured type, also indicating the return type of the transform function. |
| As a value on the right side of a SET special register statement | The data type of the special register |
| Argument of the TABLESAMPLE clause of the tablesample-clause of a table-reference | DOUBLE |
| Argument of the REPEATABLE subclause of the tablesample-clause of a table-reference | INTEGER |
| Alone as fetch-row-count in a fetch-clause | BIGINT |
| Alone as offset-row-count in an OFFSET clause | BIGINT |
| As a value in a FREE LOCATOR statement | Locator |
| As a value for the password in a SET ENCRYPTION PASSWORD statement | VARCHAR(128) |
Note:
- Determining data type from usage
- The following is an example of how the data type for an untyped
expression can be determined from subsequent usage:
If the named untyped expression is subsequently referenced in a comparison operator, it will then have the data type of the other operand. If there are multiple references of the named untyped expression in the SQL statement, the data type, length, precision, scale, and code page that is independently determined for each of those references must be identical or an error is returned.
| Untyped Expression Location | Data Type |
|---|---|
| Both operands of a comparison operator | VARCHAR(254) |
| One operand of a comparison operator, when the other operand is other than an untyped expression | The data type of the other operand |
| All operands of a BETWEEN predicate | VARCHAR(254) |
| Two operands of a BETWEEEN predicate | Same as that of the only typed expression |
| Only one operand of a BETWEEN predicate | Result of applying the Rules for result data typeson all operands that are other than untyped expressions |
| All operands of an IN predicate, for example, ? IN (?,?,?) | VARCHAR(254) |
| The first operand of an IN predicate, when the right side is a fullselect, for example, IN (fullselect) | Data type of the selected column |
| The first operand of an IN predicate, when the right side is not a subselect;, for example, ? IN (?,A,B), or ? IN (A,?,B,?) | Result of applying the "Rules for result data types" on all operands of the IN list (operands to the right of the IN keyword) that are other than untyped expressions |
| Any or all operands of the IN list of the IN predicate, for example, A IN (?,B, ?) | Result of applying the "Rules for result data types" on all operands of the IN predicate (operands to the left and right of the IN keyword) that are other than untyped expressions |
| Both the operand in a row-value-expression of an IN predicate, and the corresponding result column of the fullselect, for example, (c1, ?) IN (SELECT c1, ? FROM ...) | VARCHAR(254) |
| Any operands in a row-value-expression of an IN predicate, for example, (c1,?) IN fullselect | Data type of the corresponding result column of the fullselect |
| Any select list items in a subquery if a row-value-expression is specified in an IN predicate, for example, (c1,c2) IN (SELECT?, c1, FROM ...) | Data type of the corresponding operand in the row-value-expression |
| All three operands of the LIKE predicate | Match expression (operand 1) and pattern expression (operand 2) are VARCHAR(32672); escape expression (operand 3) is VARCHAR(2) |
| The match expression of the LIKE predicate when either the pattern expression or the escape expression is other than an untyped expression | VARCHAR(32672), VARBINARY(32672), or VARGRAPHIC(16336), depending on the data type of the first operand that is not an untyped expression |
| The pattern expression of the LIKE predicate when either the match expression or the escape expression is other than an untyped expression | VARCHAR(32672), VARBINARY(32672), or VARGRAPHIC(16336), depending on the data type of the first operand that is not an untyped expression. |
| The escape expression of the LIKE predicate when either the match expression or the pattern expression is other than an untyped expression | VARCHAR(2), VARBINARY(1), or VARGRAPHIC(1), depending on the data type of the first operand that is not an untyped expression. |
| Operand of the NULL predicate | VARCHAR(254) |
| Untyped Parameter Marker Location | Data Type |
|---|---|
| Array index of an ARRAY | BIGINT |
| All arguments of COALESCE, when all arguments are untyped parameter markers | Error |
| Any argument of COALESCE, when at least one argument is not an untyped parameter marker | Result of applying the Rules for result data typeson all arguments that are other than untyped parameter markers |
| First argument of DAYNAME | TIMESTAMP(12) |
| The argument of DIGITS | DECIMAL(31,6) |
| First argument of FROM_UTC_TIMESTAMP | TIMESTAMP(6) |
| All arguments of MAX, MIN, or NULLIF, when all arguments are untyped parameter markers | Error |
| Any argument of MAX, MIN, or NULLIF, when at least one argument is not an untyped parameter marker | Result of applying the Rules for result data typeson all arguments that are other than untyped parameter markers |
| First argument of MONTHNAME | TIMESTAMP(12) |
| POSSTR (both arguments) | Both arguments are VARCHAR(32672) |
| POSSTR (one argument, when the other argument is a character data type) | VARCHAR(32672) |
| POSSTR (one argument, when the other argument is a graphic data type) | VARGRAPHIC(16336) |
| POSSTR (the search-string argument, when the other argument is a binary data type) | VARBINARY(32672) |
| First and second arguments of REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_COUNT | VARCHAR(32672) |
| First argument of REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, or REGEXP_COUNT when second argument is not an untyped expression | VARGRAPHIC(16336) if second argument is a graphic data type; VARCHAR(32672) otherwise |
| Second argument of REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, or REGEXP_COUNT when first argument is not an untyped expression | VARGRAPHIC(16336) if first argument is a graphic data type in a Unicode database; VARCHAR(32672) otherwise |
| First three arguments of REGEXP_REPLACE | VARCHAR(32672) |
| First argument of REGEXP_REPLACE when second or third argument is not an untyped expression | VARGRAPHIC(16336) if third argument is a graphic data type or third argument is an untyped expression and second argument is a graphic data type; VARCHAR(32672) otherwise |
| Second argument of REGEXP_REPLACE when first or third argument is not an untyped expression | VARGRAPHIC(16336) if first argument is a graphic data type in a Unicode database; VARCHAR(32672) otherwise |
| Third argument of REGEXP_REPLACE when first or second argument is not an untyped expression | VARGRAPHIC(16336) if first argument is a graphic data type or first argument is an untyped expression and second argument is a graphic data type; VARCHAR(32672) otherwise |
| First argument of SUBSTR | VARCHAR(32672) |
| Second and third argument of SUBSTR | INTEGER |
| First argument of SUBSTRB | VARCHAR(32672) |
| First argument of SUBSTR2 | VARGRAPHIC(16336) if database supports graphic types; otherwise VARCHAR(32672) |
| First argument of SUBSTR4 | VARCHAR(32672) |
| Second argument of TIMESTAMP | TIME |
| First argument of TIMESTAMP_FORMAT | VARCHAR(254) |
| First argument of TIMEZONE | TIMESTAMP(6) |
| First argument of TO_UTC_TIMESTAMP | TIMESTAMP(6) |
| Second and third arguments of TRANSLATE | VARCHAR(32672) if the first argument is a character type; VARGRAPHIC(16336) if the first argument is a graphic type |
| Fourth argument of TRANSLATE | VARCHAR(1) if the first argument is a character type; VARGRAPHIC(1) if the first argument is a graphic type |
| Second argument of TRIM_ARRAY | BIGINT |
| Unary minus | DECFLOAT(34) |
| Unary plus | DECFLOAT(34) |
| All arguments of VALUE, when all arguments are untyped parameter markers | Error |
| Any argument of VALUE, when at least one argument is not an untyped parameter marker | Result of applying the Rules for result data typeson all arguments that are other than untyped parameter markers |
| First argument of VARCHAR_FORMAT | TIMESTAMP(12) |
| First argument of XMLCOMMENT | VARCHAR(32672) |
| All arguments of XMLCONCAT | XML |
| First argument of XMLDOCUMENT | XML |
| Arguments of XMLELEMENT or XMLEXISTS | Error |
| Second argument of XMLPI | VARCHAR(32672) |
| Arguments of XMLQUERY | Error |
| First argument of XMLSERIALIZE | XML |
| Arguments of XMLTABLE | Error |
| First argument of XMLTEXT | VARCHAR(32672) |
| First argument of XMLVALIDATE | XML |
| First argument of XMLXSROBJECTID | XML |
| Arguments of an aggregate function | Error |
| All other arguments of all other scalar functions | The data type of the parameter of the function definition as determined by function resolution. The length of the argument is derived based on Table 2 in Function Resolution section. |
| Untyped Parameter Marker Location | Data Type |
|---|---|
| Argument of a function | The data type and length of the parameter, as defined when the function was created. |
| Argument of a method | Error |
| Argument of a procedure | The data type of the parameter, as defined when the procedure was created |