The table below indicates data types that can be converted (assigned or compared). For data types that can be converted by using a CAST specification or a scalar function, see 2.16 Scalar functions and 2.25 CAST specification, respectively.
Table 1-5 Data types that can be converted (assigned or compared) (1/2)
Data type before conversion | Data type after conversion |
---|
Numeric data | Character data | National character data | Mixed character data | Date data | Time data | Time stamp data |
---|
INTEGER | CHARACTER | NCHAR | MCHAR | DATE | TIME | TIMESTAMP |
---|
SMALLINT |
---|
DECIMAL | VARCHAR | NVARCHAR | MVARCHAR |
---|
FLOAT |
---|
SMALLFLT | DF | EK | U16 |
---|
Numeric data
- INTEGER
- SMALLINT
- DECIMAL
- FLOAT
- SMALLFLT
| Y, Y | Y, N#1 | N, N | Y, N#1 | N, N | N, N | N, N |
Character data
| DF | C, C#2, #3 | Y, Y | C, C #14 | C, C #17 | C, C#7 | Y, Y | C, C#4 | C, C#5 | C, C#6 |
EK | C, N #15 | Y, Y | N, N | N, N | N, N |
U16 | C, C#16 | N, N | Y, Y | N, N | C, C #16 |
National character data
| N, N | N, N | Y, Y | N, N | N, N | N, N | N, N |
Mixed character data
| C, C#2, #3 | Y, Y | N, N | C, C #17 | N, N | Y, Y | N, N | N, N | N, N |
Date data
| N, N | N, C#8 | N, N | N, N | Y, Y | N, N | N, N |
Time data
| N, N | N, C#9 | N, N | N, N | N, N | Y, Y | N, N |
Time stamp data
| N, N | N, C#10 | N, N | N, N | N, N | N, N | Y, Y#11 |
Date interval data
| N, C#12 | N, N | N, N | N, N | N, N | N, N | N, N |
Time interval data
| N, C#13 | N, N | N, N | N, N | N, N | N, N | N, N |
Large object data
| N, N | N, N | N, N | N, N | N, N | N, N | N, N |
Binary data
- BINARY(n)
1 n 32,000
| N, N | N, N | N, N | N, N | N, N | N, N | N, N |
Binary data
| N, N | N, N | N, N | N, N | N, N | N, N | N, N |
Boolean data
| N, N | N, N | N, N | N, N | N, N | N, N | N, N |
Abstract data type | N, N | N, N | N, N | N, N | N, N | N, N | N, N |
- Legend:
- Y: Can be converted.
- C: Can be converted, subject to restrictions.
- N: Cannot be converted.
- DF: Default character set
- EK: EBCDIK
- U16: UTF-16, UTF-16LE, or UTF-16BE
- You must specify the character set names UTF-16LE or UTF-16BE in the following cases:
- When handling data whose character encoding is set to UTF-16 using the ? parameter
- When specifying UTF-16 data handling in the preprocessing options or embedded variable definitions in order to handle data encoding as UTF-16 in embedded variables
- For details about the UTF-16LE and UTF-16BE character sets (information on character sets that can be set in the character set descriptor area (SQLCSN)), see the HiRDB Version 9 UAP Development Guide.
- Note
- In the table, whether a given data type is convertible, is indicated in the form "assignable, comparable". For example, the entry "Y, Y" means that the data type can be assigned and compared, whereas "Y, N" means that it can be assigned but cannot be compared.
- #1: The correspondence between numeric and character data, when they are converted, is given below. For any converted data type that is mixed character data, VARCHAR reads MVARCHAR, and CHAR reads MCHAR.
- INTEGER-type numeric data is converted into VARCHAR(11) character data.
- SMALLINT-type numeric data is converted into VARCHAR(6) character data.
- DECIMAL(p,0)-type numeric data is converted into VARCHAR(p+1) character data.
- DECIMAL(p,s)-type numeric data is converted into VARCHAR(p+2) character data.
- FLOAT-type or SMALLFLT-type numeric data is converted into CHAR(23) character data.
- For INTEGER-type, SMALLINT-type, or DECIMAL-type numeric data, the converted character data does not include a positive sign. Conversion of FLOAT-type or SMALLFLT-type numeric data into character data always appends a sign to the characteristic and the mantissa.
- #2: Comparison of character data with numeric data involves a conversion of the character data into numeric data. Character data can be compared with numeric data only if the character data is present in a comparison predicate, quantified predicate, IN predicate, or BETWEEN predicate. The conditions under which a comparison can be made for each predicate are summarized in the following table:
Predicate | Comparable condition |
---|
Comparison predicate | A comparison can be made if either the right- or left-hand side of the comparison operator is numeric data. |
IN predicate, quantified predicate | A comparison can be made if the left-hand side of the comparison operator is numeric data. |
BETWEEN predicate | A comparison can be made if the row value constructor element in row value constructor 1 is numeric data. |
- Character data can be compared with numeric data if any of the following items is specified:
- Character data
- Literal (including the case where the literal is specified in a selection expression in a subquery)
- ? parameter
- Embedded variable
- SQL variable
- SQL parameter
- However, if either character or numeric data is specified in any of the following positions, the character data specified in an SQL variable or SQL parameter cannot be converted to numeric data:
- A row value constructor element in a row value constructor having two or more row value constructor elements
- A selection expression in a row or table subquery
- A selection expression in a scalar subquery that is specified in a position other than the right-hand side of a comparison predicate, IN predicate, or quantified predicate
- Numeric data
- Column specification (including the cases in which numeric data is specified in a selection expression in a subquery)
- #3: The correspondence relationship for assignment or comparison of character data with numeric data is as follows:
- Integer representations using a character string are converted into the INTEGER type.
- Decimal representations using a character string are converted into the DECIMAL type.
- Floating-point numeric representations using a character string are converted into the FLOAT type.
- A space occurring before or after a numeric character string representation is ignored during the conversion process.
- #4: The following items can be assigned to and compared with date data:
- A literal in which a date is represented in a predefined character string
- An embedded variable corresponding to CHAR(10) (or an embedded variable corresponding to CHAR(20) if the embedded variable is encoded in UTF-16, UTF-16LE, or UTF-16BE)
- #5: The following items can be assigned to and compared with time data:
- A literal in which time is represented in a predefined character string
- An embedded variable corresponding to CHAR(8) (or an embedded variable corresponding to CHAR(16) if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- #6: The following items can be assigned to and compared with time stamp data:
- A literal in which a time stamp is represented in a predefined character string
- An embedded variable corresponding to a CHAR variable with a length of 19 to 26 bytes (or an embedded variable corresponding to a CHAR variable with a length that is of 38 to 52 bytes and is a multiple of 2 if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- #7: Character string literals coded in any of the following items, exclusive of definition SQL statements, are treated as national character string literals, for which only the length of the character data is checked; the character code is not checked:
- INSERT statement
- UPDATE statement
- Search condition statement
- For details about the INSERT and UPDATE statements, see 4. Data Manipulation SQL. For details about search conditions, see 2.7 Search conditions.
- #8: The following items can be compared with date data:
- A literal in which a date is represented in a predefined character string
- An embedded variable corresponding to CHAR(10) (or an embedded variable corresponding to CHAR(20) if the embedded variable is encoded in UTF-16, UTF-16LE, or UTF-16BE)
- The following item can be converted into character data:
- Date data that is assigned to a CHAR or VARCHAR embedded variable with a minimum length of 10 bytes (or a CHAR or VARCHAR embedded variable with a length that is a minimum of 20 bytes and is a multiple of 2 if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- #9: The following items can be compared with time data:
- A literal in which time is represented in a predefined character string
- An embedded variable corresponding to CHAR(8) (or an embedded variable corresponding to CHAR(16) if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- The following item can be converted into character data:
- Time data that is assigned to a CHAR or VARCHAR embedded variable with a minimum length of 8 bytes (or a CHAR or VARCHAR embedded variable with a length that is a minimum of 16 bytes and is a multiple of 2 if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- #10: The following items can be compared with time stamp data:
- A literal in which a time stamp is represented in a predefined character string
- An embedded variable corresponding to a CHAR variable with a length of 19 to 26 bytes (or an embedded variable corresponding to a CHAR variable with a length that is of 38 to 52 bytes and is a multiple of 2 if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE)
- In addition, the following item can be assigned to character data:
- Assignment to a CHAR or VARCHAR embedded variable with a minimum length of 19 bytes if p = 0, or with a minimum length of 20 + p bytes if p > 0 where p denotes the fractional second precision of the time stamp data before conversion (or, if the embedded variable is coded in UTF-16, UTF-16LE, or UTF-16BE, assignment to a CHAR or VARCHAR embedded variable with a length that is a minimum of 38 bytes if p = 0, or 40 + 2p bytes if p > 0, and that is a multiple of 2, where p denotes the fractional second precision of the time stamp data before conversion)
- #11: If the source of assignment has a fractional second precision higher than that of the target of assignment, the fractional line part is truncated to match the precision of the target of assignment. If the source of assignment has a fractional second precision lower than that of the target of assignment, data is stored by zero-filling the expanded fractional second part to match the precision of the target of assignment.
- #12: The following items can be compared with date interval data:
- A literal in which a date interval is represented in a decimal
- An embedded variable corresponding to DECIMAL(8,0)
- The following item can be converted into decimal data:
- Date interval data that is assigned to a DECIMAL(8,0) embedded variable
- #13: The following items can be compared with time interval data:
- A literal in which a time interval is represented in a decimal.
- An embedded variable corresponding to DECIMAL(6,0)
- The following item can be assigned to decimal data:
- Time interval data that is assigned to a DECIMAL(6,0) embedded variable
- #14
- Before conversion, assignment or comparison can be performed on the following items in order to convert them to the target character set:
- Character string literal
- Embedded variable (default character set)
- #15
- After conversion, assignment can be performed on the following item in order to convert it to the target character set:
- Embedded variable (default character set)
- #16
- Before conversion, assignment or comparison can be performed on the following item in order to convert it to the target character set:
- Embedded variable (default character set)
- Before conversion, assignment or comparison can be performed on the following item in order to convert it to the target default character set:
- Embedded variable (UTF-16, UTF-16LE, or UTF-16BE)
- #17
- Before conversion, assignment or comparison can be performed on the following items in order to convert them to the target character set:
- Character string literal
- Embedded variable (default character set)
- After conversion, assignment can be performed on the following item in order to convert it to the target character set:
- Legend:
- Y: Can be converted.
- C: Can be converted, subject to restrictions.
- N: Cannot be converted.
- DF: Default character set
- EK: EBCDIK
- U16: UTF-16, UTF-16LE, or UTF-16BE
- Note
- In the table, whether a given data type is convertible is indicated in the form of "assignable, comparable." For example, the entry "Y, Y" means that the data type can be assigned and compared, whereas "Y, N" means that it can be assigned but cannot be compared.
- #1: The following items can be assigned to and compared with date interval data:
- A literal in which a date interval is represented in a decimal
- An embedded variable corresponding to DECIMAL(8,0)
- #2: The following items can be assigned to and compared with time interval data:
- A literal in which a time interval is represented in a decimal
- An embedded variable corresponding to DECIMAL(6,0)
- #3: The following items can be assigned to and compared with the BINARY type:
- Hexadecimal character string literals
- #4: Date interval data items are compared in the following order: year, month, and day.
- #5: Time interval data items are compared in the following order: hour, minute, and second.
- #6: The abstract data type ADT1 can be assigned to a column or variable defined on an abstract data type ADT2 if ADT1 is a value of ADT2 or a subtype value of the same.