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

A CAST specification 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 defined 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. The following table indicates the convertibility of each data type.

    Table 2-90 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 string data (any character set)National character dataMixed character data
    Exact numericApproximate numeric
    INTEGER, SMALLINT, DECIMALFLOAT, SMALLFLTCHAR, VARCHARNCHAR, NVARCHARMCHAR, MVARCHAR
    DFEKU16
    Numeric dataExact numericINTEGER, SMALLINT, DECIMALYYYYYNY
    Approximate numericFLOAT, SMALLFLTYYYYYNY
    Character string data (any character set)CHAR, VARCHARDFYYYY#2YNY
    EKYYY#2YNNN
    U16YYYNYNY
    National character dataNCHAR, NVARCHARNNNNNYN
    Mixed character dataMCHAR, MVARCHARYYYNYNY
    Boolean dataBOOLEANNNYYYNY
    Date dataDATENNY#1Y#1Y#1NY#1
    Time dataTIMENNY#1Y#1Y#1NY#1
    Time stamp dataTIMESTAMPNNY#1Y#1Y#1NY#1
    Date interval dataINTERVAL YEAR TO DAYYNNNNNN
    Time interval dataINTERVAL HOUR TO SECONDYNNNNNN
    Binary dataBINARYNNYYYNN
    Legend:
    Y: Data can be converted.
    N: Data cannot be converted.
    DF: Default character set
    EK: EBCDIK
    U16: UTF16
    #1
    If the length specified in the AS data type is greater than or equal to the length listed below, conversion can be performed:
    [Figure]When the character set of the result of the value expression is a character set other than UTF-16
    DATE:
    10 bytes
    TIME:
    8 bytes
    TIMESTAMP(p):
    If p = 0, 19 bytes (no periods)
    If p > 0, 20 + [Figure](p + 1) [Figure] 2[Figure] x 2 (bytes)
    [Figure]When the character set of the result of the value expression is UTF-16
    For DATE:
    [Figure]20 bytes
    For TIME:
    [Figure]16 bytes
    For TIMESTAMP(p):
    [Figure]If p = 0, 38 bytes (no periods)
    [Figure]If p > 0, 40 + [Figure](p + 1) [Figure] 2[Figure] x 4 bytes
    If the length specified in the AS data type is less than the length indicated above, conversion cannot be performed
    When converting to a fixed-length character data type and the length specified in the AS data type is greater than the length indicated above, the data is left-justified and filled with trailing spaces of the character set being used.
    #2
    SJIS characters from the default character set are treated as single-byte JIS8 characters, and conversion is performed between JIS8 encoding and EBCDIK encoding.
    For details about the character encoding conversion rules, see the HiRDB Version 9 UAP Development Guide.

    Table 2-91 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
    DATETIMETIMESTAMPINTERVAL YEAR TO DAYINTERVAL HOUR TO SECONDBINARY
    Numeric dataExact numberINTEGER, SMALLINT, DECIMALNNNYYN
    Approximate numberFLOAT, SMALLFLTNNNNNN
    Character string dataCHAR, VARCHARDFYYYNNY
    EKYYYNNY
    U16YYYNNY
    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.
    DF: Default character set
    EK: EBCDIK
    U16: UTF16
  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
(f) Binary data