Converts the data in a value expression into a specified data type.

CAST-specification::= CAST ([value-expression | NULL] AS data-type)

1. The following data types cannot be specified in value-expression:
   
   • BLOB
   • BINARY with a minimum definition length of 32,001 bytes
   • Abstract data type
2. The following data types cannot be specified in AS data-type:
   
   • BLOB
     CAST(NULL AS BLOB) can be specified.
     
   • BINARY with a minimum length of 32,001 bytes
     CAST(NULL AS BINARY(n)) can be specified, where n is a byte count with a minimum of 32,001 bytes.
     
   • BOOLEAN
   • Abstract data type
3. The value of the result is not NOT NULL constrained (the null value is allowed).
4. If NULL is specified in value-expression or the result of the value expression is the null value, the value of the result is the null value.
5. If a real length of 0 bytes or character data with a real length of 0 is specified in value-expression, conversions into a character type are performed according to the data conversion rules. Any conversion into a data type other than a character type may result in an error.
6. If an embedded variable or a ? parameter by itself is specified in value-expression for the data type of the embedded variable or the ? parameter, HiRDB assumes the data type that was specified in AS data-type.
7. When specifying a repetition column in value-expression, specify a subscript, except in the option ANY, for which a subscript cannot be specified.
8. In AS data-type, specify a data type that can be converted into the data type that is specified in value-expression. Tables 2-79 and 2-80 show data type convertibility.
   

   Table 2-79 Data type convertibility between the result of the value expression and AS data type (1/2)

   Data type of result of value expression			AS data type
   			Numeric data		Character data	National character data	Mixed character data
   			Exact numeric	Approxi- mate numeric
   			INTEGER, SMALL INT, DECIMAL	FLOAT, SMALL FLT	CHAR, VAR CHAR	NCHAR, NVAR CHAR	MCHAR,MVAR CHAR
   Numer- ic data	Exact numeric	INTEGER, SMALL INT, DECIMAL	Y	Y	Y	N	Y
   	Approx- imate numeric	FLOAT, SMALLFLT	Y	Y	Y	N	Y
   Character data		CHAR, VARCHAR	Y	Y	Y	N	Y
   National character data		NCHAR, NVARCHAR	N	N	N	Y	N
   Mixed character data		MCHAR, MVARCHAR	Y	Y	Y	N	Y
   Boolean data		BOOLEAN	N	N	Y	N	Y
   Date data		DATE	N	N	Y*	N	Y*
   Time data		TIME	N	N	Y*	N	Y*
   Time stamp data		TIMESTAMP	N	N	Y*	N	Y*
   Date interval data		INTERVAL YEAR TO DAY	Y	N	N	N	N
   Time interval data		INTERVAL HOUR TO SECOND	Y	N	N	N	N
   Binary data		BINARY	N	N	Y	N	N

   Legend:

   Y: Data can be converted.

   N: Data cannot be converted.

   ^* Conversion can be performed if the length specified in AS data-type is one of the following:

   DATE:

   10 bytes

   TIME:

   8 bytes

   TIMESTAMP(p):

   If p = 0, 19 bytes

   If p > 0, 21-26 bytes

   If the length of the character string representation of the data in the result of the value expression is 21, 23, or 25 bytes, the data is zero-filled at the end, and the respective fractional second precisions are 2, 4, and 6.
   If the data length of the area that receives results is greater than the length of the character string representation of the resulting data in the value expression, the data is left-justified and the area is filled with single-byte spaces on the right. If p = 0, a period is not used.
   

   Table 2-80 Data type convertibility between the result of the value expression and AS data type (2/2)

   Data type of result of value expression			AS data type
   			Date data	Time data	Time stamp data	Date interval data	Time interval data	Binary data
   			DATE	TIME	TIME STAMP	INTER VAL YEAR TO DAY	INTER VAL HOUR TO SECOND	BINARY
   Numeric data	Exact numeric	INTEGER, SMALLINT, DECIMAL	N	N	N	Y	Y	N
   	Approx- imate numeric	FLOAT, SMALLFLT	N	N	N	N	N	N
   Character data		CHAR, VARCHAR	Y	Y	Y	N	N	Y
   National character data		NCHAR, NVARCHAR	N	N	N	N	N	N
   Mixed character data		MCHAR,MVARCHAR	Y	Y	Y	N	N	N
   Boolean data		BOOLEAN	N	N	N	N	N	N
   Date data		DATE	Y	N	Y	N	N	N
   Time data		TIME	N	Y	Y	N	N	N
   Time stamp data		TIMESTAMP	Y	Y	Y	N	N	N
   Date interval data		INTERVAL YEAR TO DAY	N	N	N	Y	Y	N
   Time interval data		INTERVAL HOUR TO SECOND	N	N	N	Y	Y	N
   Binary data		BINARY	N	N	N	N	N	Y

   Legend:

   Y: Data can be converted.

   N: Data cannot be converted.

9. The window function cannot be specified.

(a) Numeric data

• The result in the value expression is numeric data:
  Higher effective digits should not be lost in the data type to be converted.
  
• The result in the value expression is a character string or a mixed character string:
  The result obtained by removing leading and trailing spaces from the data must be the character string representation of a numeric literal. After the character string representation of the numeric literal is converted into a numeric value, the rules for numeric data are applied to the result stored in the value expression.
  
• The result in the value expression is either date interval data or time interval data:
  If the precision and scaling of the data type (DECIMAL) of the result are as shown in Table 2-81, the data is converted into the decimal format of date interval data or time interval data.
  

  Table 2-81 Rules for the conversion of date interval data and time interval data into numeric data

  Data type of result in value expression	Length specified in AS data type
  INTERVAL YEAR TO DAY	Precision 8, scaling 0
  INTERVAL HOUR TO SECOND	Precision 6, scaling 0

(b) Character data and mixed character data

• Table 2-82 shows rules for conversion into character data and mixed character data.
  

  Table 2-82 Rules for conversion into character data and mixed character data

  Relationship between length of result of value expression and length of AS data type	Data type of result of value expression
  	Character data and mixed character data	Other than character data or mixed character data
  length-of-value-expression-result < length-specified-in-AS-data-type	If the data type specified in the AS data type is of a fixed length, the data is left-justified and filled with single-byte spaces on the right.
  length-of-value-expression-result = length-specified-in-AS-data-type	Normal conversion
  length-of-value-expression-result > length-specified-in-AS-data-type	The data is left-justified and the remainder is truncated. If the data in the part to be truncated contains characters other than a single-byte space, a 'W' is assigned to the SQLWARN1 area.*	An error may occur.

^* If a truncation occurs in the middle of a multi-byte character, a part of the multi-byte character is returned as the value of the result.

• If the result in the value expression is an exact numeric (SMALLINT, INTEGER, or DECIMAL):
  The shortest character string (with the leading zeros removed) that results from the conversion of data into a numeric literal is the result.
  If the data is less than 0, the negative sign (-) is assigned at the beginning.
  
• If the result in the value expression is an approximate numeric (SMALLFLT or FLOAT):
  The shortest character string (whose leading digit is non-zero; if the data is 0, the leading digit is 0E0) that results from the conversion of data into a numeric literal is the result.
  If the data is less than 0, the negative sign (-) is assigned at the beginning.
  
• If the result in the value expression is a character string or a mixed character string:
  If the data length of the result in the value expression is greater than the length specified in AS data type, the data is left-justified and the remainder is truncated. If the part of the data to be truncated contains characters other than a single-byte space, a 'W' is assigned to the SQLWARN1 area.
  
• If the result in the value expression is Boolean data:
  If the result in the value expression is true, the conversion result is 'TRUE'; if the result in the value expression is false, the conversion result is 'FALSE'. If the result in the value expression is unknown, the conversion result is the null value.
  
• If the result in the value expression is date data, time data, or time stamp data:
  The data is converted into the defined character string representation of date data, time data, or time stamp data.
  
• If the result in the value expression is binary data:
  If the data length of the result in the value expression is greater than the data length specified for the data type of the result, a 'W' is assigned to the SQLWARN1 area.
  

(c) National character data

• If the result in the value expression is a national character string:
  If the data type of the result is of a fixed length, and the result of converting the data in the value expression is less than the length specified in AS data-type, the data is filled with double-byte spaces that correspond to the character code.
  If the result of converting the data in the value expression into a national character string is greater than the data length specified for the data type of the result, the data is left-justified and truncated on the right. If the part of the data to be truncated contains characters other than a double-byte space corresponding to the character code, a 'W' is assigned to the SQLWARN1 area.
  

(d) Date data, time data, and time stamp data

• If the result in the value expression is a character string or a mixed character string:
  The data can be converted if it is the defined character string representation of date data, time data, or time stamp data. An error may occur if the result of removing leading and trailing single-byte spaces from the data in the value expression has a fractional second precision other than 0, 2, 4, or 6.
  
• If the result in the value expression is date data, time data, or time stamp data:
  Conversion can be performed according to the combinations shown in Table 2-83.
  

  Table 2-83 Conversion rules for date data, time data, and time stamp data

  Data type of result of value expression	AS data type	Conversion rule
  DATE	DATE	Not converted.
  	TIMESTAMP(p2)	The time part is converted as '00:00:00'. The fractional second part is zero-filled.
  TIME	TIME	Not converted.
  	TIMESTAMP(p2)	The date part is converted into CURRENT_DATE. The fractional second part is zero-filled.
  TIMESTAMP(p1)	DATE	Extracts and converts the date part.
  	TIME	Extracts and converts the time part.
  	TIMESTAMP(p2)	Not converted. If p1 > p2, the fractional second part is truncated, and if p1 < p2, it is zero-filled.

(e) Date interval data and time interval data

• If the result in the value expression is DECIMAL:
  If the precision and scaling are as indicated in Table 2-84, numeric data can be converted into the corresponding date interval data or time interval data.
  

  Table 2-84 Conversion rules for numeric data into date interval data or time interval data

  Format of numeric data	Data type of result of value expression
  Precision 8, scaling 0	INTERVAL YEAR TO DAY
  Precision 6, scaling 0	INTERVAL HOUR TO SECOND

• If the result in the value expression is date interval data or time interval data:

Conversions can be performed according to the combinations shown in Table 2-85.

Table 2-85 Conversion rules for date interval data and time interval data

Data type of result of value expression	AS data type	Conversion rule
INTERVAL YEAR TO DAY	INTERVAL YEAR TO DAY	Not converted.
	INTERVAL HOUR TO SECOND	The date part is converted into a time part, and if the result does not exceed the range of values for INTERVAL HOUR TO SECOND, the data can be converted. If the result exceeds this range, an error may occur.
INTERVAL HOUR TO SECOND	INTERVAL YEAR TO DAY	If the time part is greater than 24 hours, it is carried to the date part. Any data less than 24 hours is truncated.
	INTERVAL HOUR TO SECOND	Not converted.

(f) Binary data

• If the result of the value expression is a character string:
  If the conversion of the data in the value expression into BINARY produces a result that exceeds the length specified in AS data-type, the value 'W' is assigned to the SQLWARN1 area.