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.
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.
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-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 types" to 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 types" to 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 types" to 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 types" on 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 |
As a value in a FREE LOCATOR statement | Locator |
As a value for the password in a SET ENCRYPTION PASSWORD statement | VARCHAR(128) |
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 types" on 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 | Either VARCHAR(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 | Either VARCHAR(32672) or VARGRAPHIC(16336), depending on the data type of the first operand that is not an untyped expression; if the data type of the match expression is BLOB, the data type of the pattern expression is assumed to be BLOB(32672) |
The escape expression of the LIKE predicate when either the match expression or the pattern expression is other than an untyped expression | Either VARCHAR(2) or VARGRAPHIC(1), depending on the data type of the first operand that is not an untyped expression; if the data type of the match expression or pattern expression is BLOB, the data type of the escape expression is assumed to be BLOB(1) |
Operand of the NULL predicate | VARCHAR(254) |
Untyped Parameter Marker Location | Data Type |
---|---|
All arguments of COALESCE, MIN, MAX, NULLIF, or VALUE | Error |
Any argument of COALESCE, MIN, MAX, NULLIF, or VALUE, when at least one argument is other than an untyped parameter marker | Result of applying the "Rules for result data types" on 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 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 BLOB) | BLOB(32672) |
STRIP (one argument and three arguments
when strip-character is character data type) [Version 10.1 Fix Pack 5 or later] |
VARCHAR(32672) |
STRIP (three arguments when
strip-character is a graphic data type) [Version 10.1 Fix Pack 5 or later] |
VARGRAPHIC(16336) |
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) |
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 |
TRIM (one argument and three arguments
when strip-character is character data type) [Version 10.1 Fix Pack 5 or later] |
VARCHAR(32672) |
TRIM (three arguments when
strip-character is a graphic data type) [Version 10.1 Fix Pack 5 or later] |
VARGRAPHIC(16336) |
The second argument of TIMESTAMP | TIME |
First argument of VARCHAR_FORMAT | TIMESTAMP(12) |
First argument of TIMESTAMP_FORMAT | VARCHAR(254) |
First argument of XMLVALIDATE | XML |
First argument of XMLCOMMENT | VARCHAR(32672) |
First argument of XMLTEXT | VARCHAR(32672) |
Second argument of XMLPI | VARCHAR(32672) |
First argument of XMLSERIALIZE | XML |
First argument of XMLDOCUMENT | XML |
First argument of XMLXSROBJECTID | XML |
All arguments of XMLCONCAT | XML |
Second argument of TRIM_ARRAY | BIGINT |
Array index of an ARRAY | BIGINT |
Unary minus | DECFLOAT(34) |
Unary plus | DECFLOAT(34) |
Arguments of a aggregate function | Error |
Arguments of XMLEXISTS, XMLELEMENT, XMLTABLE or XMLQUERY | 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 |