DB2 Version 9.7 for Linux, UNIX, and Windows

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 tables below. For data types not listed as a target type in Table 2 in "Functions", 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.

Table 1. Untyped Expression Usage in Expressions (Including Select List, CASE, and VALUES)
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: `(? + ?) + 10`	DECFLOAT(34)
One operand of a single operator in an arithmetic expression (not a datetime expression) Includes cases such as: `? + (? * 10)`	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)

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.

Table 2. Untyped Expression Usage in Predicates
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 hand side is a fullselect, for example, IN (fullselect)	Data type of the selected column
The first operand of an IN predicate, when the right hand 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)

Table 3. Untyped Expression Usage in Built-in Functions
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 9.7 Fix Pack 10 or later]	VARCHAR(32672)
STRIP (three arguments when strip-character is a graphic data type) [Version 9.7 Fix Pack 10 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 9.7 Fix Pack 10 or later]	VARCHAR(32672)
TRIM (three arguments when strip-character is a graphic data type) [Version 9.7 Fix Pack 10 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.

Table 4. Untyped Expression Usage in User-defined Routines
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