6.2.2 Data types that can be converted, assigned, and compared

Organization of this subsection

(1) Data types that can be compared
(2) Storage assignments between data types
(3) Search assignments of data types
(4) Storage assignment to a table function derived table (in the case of the ADB_CSVREAD function)

(1) Data types that can be compared

The following table shows the combinations of data types that can be compared.

Table 6‒6: Combinations of data types that can be compared
Data type		Data type of comparison target
		Numeric data	Character string data	Datetime data			Binary data
		Numeric data	Character string data	DATE	TIME	TIMESTAMP	Binary data
Numeric data		Y	N	N	N	N	N
Character string data		N	Y	Y	Y	Y	N
Datetime data	`DATE`	N	Y	Y	N	Y	N
	`TIME`			N	Y	N	N
	`TIMESTAMP`			Y	N	Y	N
Binary data		N	N	N	N	N	Y

Legend:

Y: Can be compared.

N: Cannot be compared.

■ Comparing character string data

If the lengths of the character string data being compared are different, spaces are added to the end of the shorter data string to make the lengths the same, and then the comparison is performed.
Even when comparing VARCHAR types, the comparison is performed after the spaces are added.

■ Comparing numeric data

If the data types of the data being compared are different, the comparison is performed using the data type that has the larger range. The range sizes are ordered as follows:

DOUBLE PRECISION > DECIMAL > INTEGER > SMALLINT

■ Comparing datetime data to character string data

Datetime data can be compared to character string data only when the character string data is a literal written in the corresponding predefined input representation. For information about predefined input representations, see 6.3.3 Predefined character-string representations.

Date data can be compared to character strings written in the predefined input representation for date data. The character string data in the predefined input representation for date data is converted to date data, and then the comparison is performed on the date data items.
Date data can be compared to character strings written in the predefined input representation for time stamp data. A time of 0 hours, 0 minutes, and 0 seconds is set to the date data, and the date data is converted to time stamp data. The character string data in the predefined input representation for time stamp data is then converted to time stamp data. The comparison is then performed on the time stamp data items.
Time data can be compared to character strings written in the predefined input representation for time data. The character string data in the predefined input representation for time data is converted to time data, and then the comparison is performed on the time data items.
Time stamp data can be compared to character strings written in the predefined input representation for time stamp data. The character string data in the predefined input representation for time stamp data is converted to time stamp data, and then the comparison is performed on the time stamp data items.
Time stamp data can be compared to character strings written in the predefined input representation for date data. A time of 0 hours, 0 minutes, and 0 seconds is set to the predefined input representation for date data, and the character string data is converted to time stamp data. The comparison is then performed on the time stamp data items.

However, if the datetime data is located in the selection expression of a subquery, it cannot be compared to the corresponding value expression.

■ Comparing datetime data

When date data and time stamp data are compared, the date data is converted into time stamp data by setting the time to 0 hours, 0 minutes, and 0 seconds.
When the number of digits in the fractional seconds are different, the lower-precision fractional seconds are padded with zeros until they align with the higher-precision data.

■ Comparing binary data

When the data to be compared have the same length, they are considered equal when all the byte values match.

Example:

In the above case, binary data X = binary data Y.
When the data to be compared have different lengths, they are considered equal when the following two conditions are met:

• All the byte values match when compared from the first byte through the end of the shorter data

• The byte values in the longer portion are all X'00'

Example:

In the above case, binary data X = binary data Y.
The data are compared in order starting from the first byte. When the byte values differ, the magnitudes of the first bytes that are different are compared, and this is used to determine which is greater.

Example:

In the above case, binary data X < binary data Y.
When the data to be compared have different lengths, and the byte values match from the first byte through the end of the shorter data, which value is greater is determined as follows.

Let X be the shorter data and Y be the longer data. If there are one or more byte values other than X'00' in the longer portion of Y, then X < Y.

Example:

In the above case, binary data X < binary data Y.

To Page Top

(2) Storage assignments between data types

The table below lists the combinations of data types that can be specified as an insertion value in an INSERT statement or an update value in an UPDATE statement. However, if you use a dynamic parameter to perform the storage assignment, align data types of the assignment source and assignment target. For details about dynamic parameters, see 6.6 Variables (dynamic parameters).

Table 6‒7: Storage assignment relationships between combinations of data types
Data type of assignment source		Data type of assignment target
		Numeric data	Character string data	Datetime data			Binary data	Row data
		Numeric data	Character string data	DATE	TIME	TIMESTAMP	Binary data	Row data
Numeric data		Y	N	N	N	N	N	N
Character string data		N	Y	Y	Y	Y	N	Y^#
Datetime data	`DATE`	N	N	Y	N	Y	N	N
	`TIME`			N	Y	N	N
	`TIMESTAMP`			Y	N	Y	N
Binary data		N	N	N	N	N	Y	N

Legend:

Y: Storage assignment can be performed.

N: Storage assignment cannot be performed.

#

A CHAR type can be assigned to row data (ROW).

■ Storage assignment of character string data

If the data length of the assignment source is longer than the data length of the assignment target, the assignment cannot be performed.
If the assignment target is CHAR type, and the data length of the assignment source is shorter than the data length of the assignment target, it is stored with spaces added at the end.

■ Storage assignment of numeric data

If the assignment source exceeds the range of values that can be handled by the assignment target, the assignment cannot be performed.
If the assignment target is INTEGER or SMALLINT, and the assignment source is DECIMAL or DOUBLE PRECISION, the fractional (decimal) part is truncated.
If the assignment source and assignment target are both DECIMAL, any digits of the assignment source that are beyond the scaling of the assignment target are truncated. If the assignment source scaling is smaller than the assignment target scaling, it is stored with zeros added to the fractional part.
If the assignment source is DOUBLE PRECISION and assignment target is DECIMAL, any digits of the assignment source that are beyond the scaling of the assignment target are rounded off (to the nearest even number). If the assignment source scaling is smaller than the assignment target scaling, it is stored with zeros added to the end of the fractional part.

■ Changing the storage assignment from character string data to datetime data

Changing the storage assignment from character string data to datetime data is possible only when the character string data is a literal written in the corresponding predefined input representation. For information about predefined input representations, see 6.3.3 Predefined character-string representations.

However, changing the storage assignment is not possible when the datetime data is a column that is to be inserted by an INSERT statement and the character string is specified as the selection expression of a query specification, even when the character string is a literal written in the corresponding predefined input representation.

The storage assignment of character strings written in the predefined input representation for date data can be changed to date data. The character string data in the predefined input representation for date data is converted to date data, and then its storage assignment is changed to date data.
The storage assignment of character strings written in the predefined input representation for time stamp data can be changed to date data. In this case, the character string data in the predefined input representation for time stamp data is converted to time stamp data, and then the storage assignment is changed for only the date portion of the time stamp data.
The storage assignment of character strings written in the predefined input representation for time data can be changed to time data. The character string data in the predefined character-string representation for time data is converted to time data, and then its storage assignment is changed to time data.
The storage assignment of character strings written in the predefined input representation for time stamp data can be changed to time stamp data. The character string data in the predefined input representation for time stamp data is converted to time stamp data, and then its storage assignment is changed to time stamp data.
The storage assignment of character strings written in the predefined input representation for date data can be changed to time stamp data. In this case, a time of 0 hours, 0 minutes, and 0 seconds is set to the predefined character-string representation for date data, and the character string data is converted to time stamp data. Its storage assignment is then changed to time stamp data.

■ Storage assignment of datetime data

When the storage assignment of date data is changed to time stamp data, a time of 0 hours, 0 minutes, and 0 seconds is set to the date data. Its storage assignment is then changed to time stamp data.
When the source is time stamp data and the target is date data, storage assignment of only the date portion of the time stamp data is performed.
If the number of digits in the fractional seconds of the assignment source is greater than the number of digits in the fractional seconds of the assignment target, the portion that cannot be assigned is truncated.
If the number of digits in the fractional seconds of the assignment source is less than the number of digits in the fractional seconds of the assignment target, storage assignment is performed by padding the excess portion with 0.

■ Storage assignment of binary data

If the data length of the source is greater than the data length of the target, the assignment cannot be performed.
If the target type is BINARY and the data length of the source is less than the data length of the target, the data is padded with X'00' at the end before it is stored.

■ Storage assignment of row data

Match the data length of the assignment source to the assumed row data length of the assignment target (the row length of the table being updated or inserted into).

To Page Top

(3) Search assignments of data types

If you receive retrieval results, be sure to align the data types of the assignment source and assignment target.

If ROW (row data) is specified in the selection expression, the data type of the assignment target for receiving the search result can be a CHAR type variable.

To Page Top

(4) Storage assignment to a table function derived table (in the case of the ADB_CSVREAD function)

This subsection describes the rules for how field data in a CSV file is assigned to columns in a table function derived table. It also gives the rules for the description format of the field data.

Note that the table function derived table here means a table function derived table derived by means of the ADB_CSVREAD function.

Note: A table function derived table is a collection of data in table format derived by means of the ADB_AUDITREAD function or the ADB_CSVREAD function. For details about the ADB_AUDITREAD function, see 7.15.2 ADB_AUDITREAD function. For details about the ADB_CSVREAD function, see 7.15.3 ADB_CSVREAD function.

The description format of the field data must be compatible with the data type of the column in the table function derived table. The following table shows the relationship between the data type of the column in the table function derived table and the description format of the field data.

Table 6‒8: Relationship between the data type of the column in the table function derived table and the description format of the field data
No.	Data type of the column in the table function derived table		Description format of the field data
No.	Data type of the column in the table function derived table		Format	Examples	Notes	Examples of storage assignment of the null value
1	Numeric data	`INTEGER`	`[{+\|-}]a...a` `+`, `-`: Sign `a`...`a`: Numeric value (`a` is `0` to `9`)	`100` `-123` `000` `0657`	The sign and numeric value together cannot exceed 20 characters. Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	...`,,`... ...`,"",`... ...`,,`... ...`,"",`... ...`,"""",`... However, the examples using enclosing characters are invalid if the enclosing character specification option is set to `NONE`.
2		`SMALLINT`	`[{+\|-}]a...a` `+`, `-`: Sign `a`...`a`: Numeric value (`a` is `0` to `9`)	`100` `-0123` `0` `+0657`	The sign and numeric value together cannot exceed 11 characters. Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
3		`DECIMAL`	`[{+\|-}]` {`a...a[.[b...b]]\|.b...b}` `+`, `-`: Sign `a`...`a`: Integer part (`a` is `0` to `9`) `b`...`b`: Fractional part (`b` is `0` to `9`)^#2	`100` `-123.00` Δ`.00` 012. `-1.56` `+.560`	The integer and fractional parts together cannot exceed 38 characters (or 39 characters if the integer part (0) is omitted and the precision and scaling match the column where the value is to be stored). Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
4		`DOUBLE` `PRECISION`	`[{+\|-}]` `{a...a[.[b...b]]\|.b...b}[{E\|e}[[{+\|-}]c...c]]` `+`, `-`: Sign `a`...`a`: Integer part of the mantissa (`a` is `0` to `9`) `b`...`b`: Fractional part of the mantissa (`b` is `0` to `9`) `c`...`c`: Exponent (`c` is `0` to `9`)^#3 `E`, `e`: Floating point numeric literal (literal identifying the exponent in a floating-point number)	`100` `-`Δ `123` `0.`Δ `-1.5600` `.56` `-02.4e+9` `000e` `2.4E+009`	The data cannot exceed 509 characters.^#4 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
5	Character string data	`CHARACTER`	`a...a` `a`...`a`: Data consisting of one or more characters	`abcdef` `ABCDEF` Δ	The number of characters cannot exceed the defined length of the column where the value is to be stored. The trailing single-byte spaces in the examples can be omitted.^#5	...`,,`... ...`,"",`... The character string data cannot include single-byte spaces and tabs. The examples using enclosing characters are invalid if the enclosing character specification option is set to `NONE`.
6	Character string data	`VARCHAR`	`a...a` `a`...`a`: Data consisting of one or more characters	`abcdef` `ABCDEF`	The number of characters cannot exceed the defined length of the column where the value is to be stored.	...`,,`... Enclosing characters cannot be specified. The character string data cannot include single-byte spaces and tabs. ■ To specify data of length 0 ...`,"",`... However, data of length 0 cannot be specified if the enclosing character specification option is set to `NONE`.
7	Datetime data	`DATE`	`{YYYY-MM-DD` `\|YYYY/MM/DD}` `YYYY`: Year (`0001` to `9999`) `MM`: Month (`01` to `12`) `DD`: Day (`01` to last day of month)	`2013-06-10` `2013/06/10`	Regardless of the format, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
8		`TIME`	`hh:mm:ss[.[nn...n]]` `hh`: Hour (`00` to `23`) `mm`: Minutes (`00` to `59`) `ss`: Seconds (`00` to `59`) `nn`...`n`: Fractional seconds (`n` is `0` to `9`)	`11:03:58` 11:03:58. `11:03:58` Δ`.1234`	The fractional seconds (`nn`...`n`) cannot exceed 12 characters.^#6 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
9		`TIMESTAMP`	`{YYYY-MM-DD` `\|YYYY/MM/DD}` Δ`hh:mm:ss[.[nn...n]]` `YYYY`: Year (`0001` to `9999`) `MM`: Month (`01` to `12`) `DD`: Day (`01` to last day of month) `hh`: Hour (`00` to `23`) `mm`: Minutes (`00` to `59`) `ss`: Seconds (`00` to `59`) `nn`...`n`: Fractional seconds (`n` is `0` to `9`)	`2013-06-10` Δ `11:03:58` `2013-06-10` Δ `11:03:58` Δ `.1234`	The fractional seconds (`nn`...`n`) cannot exceed 12 characters.^#6 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
10	Binary data	`BINARY`	Hexadecimal string `a...a` `a`: `0` to `9`, `A` to `F`, or `a` to `f`	`12340000` `90`Δ `AB` `90ab` Δ `CDEF`	The number of characters must be a multiple of 2, up to 2 times the defined length of the column where the value is to be stored.^#7 Trailing `00`s are assumed and can be omitted.^#8 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
11		`BINARY`	Binary string `a...a` `a`: `0` or `1`	`01010101` `0101` Δ `0101`	The number of characters must be a multiple of 8, up to 8 times the defined length of the column where the value is to be stored.^#7 Trailing `00000000`s are assumed and can be omitted.^#8 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 1.
12		`VARBINARY`	Hexadecimal string `a...a` `a`: `0` to `9`, `A` to `F`, or `a` to `f`	`12340000` `90`Δ `AB` `90ab` Δ `CDEF`	The number of characters must be a multiple of 2, up to 2 times the defined length of the column where the value is to be stored.^#7 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	...`,,`... ...`,"",`... ...`,,`... However, the examples using enclosing characters are invalid if the enclosing character specification option is set to `NONE`. ■ To specify data of length 0 ...`,"",`... ...`,"""",`... However, data of length 0 cannot be specified if the enclosing character specification option is set to `NONE`.
13		`VARBINARY`	Binary string `a...a` `a`: `0` or `1`	`01010101` `0101` Δ `0101`	The number of characters must be a multiple of 8, up to 8 times the defined length of the column where the value is to be stored.^#7 Regardless of the format and length restrictions, leading and trailing single-byte spaces and tabs are permitted around all characters.^#1	Same as No. 12.

Legend:

Δ: One or more single-byte spaces or tabs

,: Delimiting character

": Enclosing character

#1

Any leading or trailing single-byte space (0x20) or tab (0x09) characters are removed.

Example: Δ1Δ23 [Figure] 4 → 1234

If the removal of white space leaves no data, the result is treated as a null value.

#2

Fractional digits beyond the scaling defined for the column where the value is to be stored are truncated.

#3

If the exponent is omitted, an exponent of +0 is assumed.

#4

Depending on the specified value, loss of precision might occur.

#5

If the input data is less than the defined length, the remaining portion is filled with single-byte spaces.

#6

If the number of digits in the fractional seconds (nn...n) is less than the fractional seconds precision of the data type in the table, the stored value is filled with zeros on the right.

If the number of digits in the fractional seconds (nn...n) exceeds the fractional seconds precision of the data type in the table, the input data is truncated.

#7

An error results if the number of characters in the hexadecimal string is not a multiple of 2.

An error results if the number of characters in the binary string is not a multiple of 8.

#8

When the input data is less than the defined length, the remaining portion is filled with 0x00.

To Page Top