2.25 CAST specification

Organization of this section
(1) Function
(2) Format
(3) Rules
(4) Conversion rules specific to the data types of results

(1) Function

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

(2) Format

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

(3) Rules

  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 expressionAS data type
    Numeric dataCharacter dataNational character dataMixed character data
    Exact numericApproxi- mate numeric
    INTEGER, SMALL INT, DECIMALFLOAT, SMALL FLTCHAR, VAR CHARNCHAR, NVAR CHARMCHAR,MVAR CHAR
    Numer- ic dataExact numericINTEGER, SMALL INT, DECIMALYYYNY
    Approx- imate numericFLOAT, SMALLFLTYYYNY
    Character dataCHAR, VARCHARYYYNY
    National character dataNCHAR, NVARCHARNNNYN
    Mixed character dataMCHAR, MVARCHARYYYNY
    Boolean dataBOOLEANNNYNY
    Date dataDATENNY*NY*
    Time dataTIMENNY*NY*
    Time stamp dataTIMESTAMPNNY*NY*
    Date interval dataINTERVAL YEAR TO DAYYNNNN
    Time interval dataINTERVAL HOUR TO SECONDYNNNN
    Binary dataBINARYNNYNN
    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 expressionAS data type
    Date dataTime dataTime stamp dataDate interval dataTime interval dataBinary data
    DATETIMETIME STAMPINTER VAL YEAR TO DAYINTER VAL HOUR TO SECONDBINARY
    Numeric dataExact numericINTEGER, SMALLINT, DECIMALNNNYYN
    Approx- imate numericFLOAT, SMALLFLTNNNNNN
    Character dataCHAR, VARCHARYYYNNY
    National character dataNCHAR, NVARCHARNNNNNN
    Mixed character dataMCHAR,MVARCHARYYYNNN
    Boolean dataBOOLEANNNNNNN
    Date dataDATEYNYNNN
    Time dataTIMENYYNNN
    Time stamp dataTIMESTAMPYYYNNN
    Date interval dataINTERVAL YEAR TO DAYNNNYYN
    Time interval dataINTERVAL HOUR TO SECONDNNNYYN
    Binary dataBINARYNNNNNY
    Legend:
    Y: Data can be converted.
    N: Data cannot be converted.
  9. The window function cannot be specified.

(4) Conversion rules specific to the data types of results

(a) Numeric data
(b) Character data and mixed character data
* 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.
(c) National character data
(d) Date data, time data, and time stamp data
(e) Date interval data and 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 expressionAS data typeConversion rule
INTERVAL YEAR TO DAYINTERVAL YEAR TO DAYNot converted.
INTERVAL HOUR TO SECONDThe 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 SECONDINTERVAL YEAR TO DAYIf 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 SECONDNot converted.
(f) Binary data