SQL-J Language Reference
Page 63 of 121

CAST

CAST converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value.

CAST expressions are permitted anywhere expressions are permitted.

Syntax

CAST ( [ Expression | NULL | ? ]
    AS { DataType | CLASS JavaClassName | ClassAlias } )

The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.

CAST Example

SELECT *
FROM CustomizedTours
WHERE customized_tour = CAST
    (? AS CLASS JBMSTours.serializabletypes.Tour)

CAST Conversions Among SQL-92 Data Types

Figure 1-1 shows valid conversions between source types and target types for SQL-92 data types.

Figure 1-1 Valid CAST conversions between SQL-92 data types

For information about conversions among Java types, see CAST Conversions of Java Data Types.

If a conversion is valid, CASTs are allowed. Size incompatibilities between the source and target types may cause runtime errors. For example, converting from a boolean to a CHAR is valid. However, a boolean requires at least four characters to be represented as a string. Casting a boolean as a CHAR(3) causes a runtime error.

Notes About CAST Conversions

In this discussion, the Cloudscape SQL-92 data types are categorized as follows:

CAST Conversions to Boolean

Strings and numerics can be converted to booleans.

  • For numerics, if the value is 0, the CAST evaluates to false; otherwise, the CAST evaluates to true.
  • For bit strings, if the string evaluates to X'0', the CAST evaluates to false; otherwise, the CAST evaluates to true.

    -- returns false
    VALUES CAST (X'000' AS boolean);

    -- returns true
    VALUES CAST (X'001' AS boolean);

    -- returns true
    VALUES CAST (X'002' AS boolean);

  • For character strings, "false" (case-insensitive) evaluates to false, and "true" (case-insensitive) evaluates to true. All other strings raise an error (this corresponds to the SQL-92 standard but differs from Java behavior).

A date/time value cannot be converted to a boolean.

Booleans can be compared to any numeric types, and you can assign a numeric type to a boolean and vice versa. See Comparing Booleans with Numeric Types and Assigning Numeric Types to Booleans.

CAST and Implicit Conversions to Character Strings

Cloudscape supports implicit conversions of character strings to all the built-in data types and vice-versa. In many situations, you do not need to CAST. For example:

-- 1 converted to '1'
INSERT INTO charColumn VALUES 1

-- 1 converted to '1', booleanColumn converted to string type
SELECT 1 || booleanColumn FROM t1

For information on when a value is implicitly converted to a String, see Implicit Type Conversion of String Types.

Any built-in type can be converted to a character string, provided that the target character string has room to represent the source data type.

With the exception in some cases of the international string data types, the resulting data value conforms to the definition of a literal for the source data type. For example, when a TIME value is converted to a character string, the resulting string is of the format HH:MM:SS. The exception is the BIT data type, which when converted is interpreted as a bit representation of a Unicode character string and is converted directly into the target string. For example, CAST(X'0061' AS VARCHAR(10)) yields "a" instead of the character string "X'0061'".

For information about international string data types, see Table , "Conversions Between Date/Time Data Types and International String Data Types".

Size Requirements for Explicit and Implicit Conversions to Character Strings

  • Booleans require four characters to represent true and five characters to represent false.
  • Numeric values require one character for each digit to the left and each digit to the right of the decimal point and an additional character for negative numbers to represent the minus sign. They also require a character for the decimal point. Approximate numerics may require an additional 3 or 4 characters for the exponent part (3 for a positive exponent, 4 for a negative exponent). For example, DOUBLE PRECISION and FLOAT data types (with a precision greater than or equal to 24) may require the extra characters. Truncation of nonzero digits will raise an exception.

    -- returns 1
    VALUES CAST (1.00000 AS CHAR(3));

  • DATE values require 10 characters (8 for digits and 2 for dashes).
  • TIME values require 8 characters (6 digits and 2 colons).
  • TIMESTAMP fields require 23 characters (1 space, 2 dashes, 2 colons, 1 decimal point, and 17 digits). For TIMESTAMP, the milliseconds portion of the timestamp may be silently truncated if necessary.
  • The length required by bit strings depends on the string itself (16 bits per character, using the Unicode character encoding); you can use this formula:

    BIT_LENGTH(bitExpression)/16

    Padding is performed automatically when the target data type is larger than the source data value. Spaces are used to pad character and date/time values, and zeroes are used for all other types.

Implicit conversion to a string is not supported for the receiver or arguments to a Java method.

CAST and Implicit Conversions from Character Strings

Cloudscape supports implicit conversions of character strings to all the built-in data types and vice-versa. In many situations you do not need to CAST.

For example:

-- '1' implicitly converted to 1 when compared to integer
SELECT *
FROM t1
WHERE intColumn = '1'

-- charColumn implicitly converted to boolean type
-- when compared to boolean
SELECT *
FROM t1 booleanColumn = charColumn

-- '1' implicitly converted to int when used in INSERT
INSERT INTO t1 (intColumn) VALUES '1'

-- '2' converted to int
SELECT 1 + '2'
FROM t1

-- charColumn converted to boolean when used as search condition
SELECT *
FROM t1 WHERE charColumn

-- string '1998-01-01' converted to date
INSERT INTO t1 (dateColumn) VALUES '1998-01-01'

For information on when a String is implicitly converted to a non-String data type, see Implicit Type Conversion of String Types.

For both explicit and implicit conversions, an exception is thrown if the String cannot be converted to the target data type. The basic rules for explicit and implicit conversion from character strings are as follows:

  • Invalid characters

    A character string can be converted to any data type, but if the string contains an invalid character or invalid formatting, an exception is raised. An invalid character is one that is inappropriate for the target data type. For example, 'hello' cannot be converted to an integer. Formatting that is not consistent with the definition of a literal data value of the target type is invalid. For example, for a string to convert successfully to a TIME data type, the source string must contain three sets of two base-10 digits separated by semicolons. It is not necessary for the string to have the type identifier, though either is acceptable (both TIME'11:11:11' and '11:11:11' yield the same TIME value during conversion). Another example: For a string to convert successfully to an integer data type, it must not contain a decimal point.

    ij> VALUES 1 + '1.1';
    SQLCol1
    ---------------
    ERROR 22018: Type INTEGER does not recognize the format of the string '1.1'.
    ij> VALUES 1 + CAST ('1.1' AS INT);
    SQLCol1
    ---------------
    ERROR 22018: Type INTEGER does not recognize the format of the string '1.1'.
    ij> -- implicit conversion of '1.0' to INT fails
    ij> VALUES 2 > '1.0';
    SQLC&
    -----
    ERROR 22018: Type INT does not recognize the format of the string '1.0'.

  • Leading and trailing spaces

    Conversion from a character string to any other noncharacter data type strips out all leading and trailing spaces before the conversion is performed. If the target is a bit string, each character is converted into the corresponding 16-bit Unicode representation, with possible truncation of trailing spaces if they don't fit into the resulting bit string, or padding with bits set to zero as needed. Character-string-to-character-string conversions retain leading spaces but may truncate or pad trailing spaces as needed.

For information about some additional limitations on implicit conversion, see Implicit Type Conversion of String Types.

CAST Conversions from Numeric Types

A numeric type can be converted to any other numeric type. If the target type cannot represent the non-fractional component without truncation, an exception is raised. If the target numeric cannot represent the fractional component (scale) of the source numeric, then the source is silently truncated to fit into the target. For example, casting 763.1234 as INTEGER yields 763.

CAST Conversions from and to Bit Strings

Bit strings can be converted to other bit strings as well as character strings and booleans. Strings that are converted to bit strings are padded with trailing zeros to fit the size of the target bit string.

CAST Conversions of Date/Time Values

A date/time value can always be converted to and from a TIMESTAMP. If a DATE is converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is converted to a TIME, the DATE component is silently truncated.

Conversions Between Date/Time Data Types and International String Data Types

For international string data types, implicit and explicit conversions from the DATE, TIME, and TIMESTAMP data types to the international string data types use the LONG format of java.text.DateFormat. Table 1-4, "Conversion Between Date/Time Data Types and International String Data Types" shows how conversions to International String data types are different from conversions to regular string types:

Table 1-4 Conversion Between Date/Time Data Types and International String Data Types

Data Type

Example of CAST to International String Data Type (English Locale)

Example of CAST to non-International String Data Type

TIME

9:51:46 AM GMT

09:54:33

DATE

May 9, 2000

2000-05-09

TIMESTAMP

May 9, 2000 9:52:57 AM GMT

2000-05-09 09:53:40.427

Cloudscape also supports explicit and implicit conversions from such locale-specific strings into DATE, TIME, and TIMESTAMP data types. The following example is from a German locale:

CREATE TABLE MYDATES (
    realdate DATE,
    StringDate VARCHAR(20),
    NStringDate NVARCHAR(20));
0 rows inserted/updated/deleted ij> -- an implicit CAST to a standard String and to -- an international string type from a DATE type INSERT INTO MYDATES VALUES (CURRENT_DATE, CURRENT_DATE, CURRENT_DATE); 1 row inserted/updated/deleted ij> -- note that the third column, an international string date, was -- converted using the LONG format of java.text.DateFormat SELECT * FROM MYDATES; REALDATE |STRINGDATE |NSTRINGDATE ---------------------------------------------------- 2000-05-08|2000-05-08 |8. Mai 2000 1 row selected ij> -- an implicit CAST from a standard string type to a DATE INSERT INTO MYDATES (realdate) (SELECT StringDate FROM MYDATES); 1 row inserted/updated/deleted ij> -- an implicit CAST from an international string type to a DATE INSERT INTO MYDATES (realdate) (SELECT NSTringDate FROM MYDATES); 2 rows inserted/updated/deleted ij> SELECT * FROM MYDATES; REALDATE |STRINGDATE |NSTRINGDATE ---------------------------------------------------- 2000-05-08|2000-05-08 |8. Mai 2000 2000-05-08|NULL |NULL 2000-05-08|NULL |NULL NULL |NULL |NULL 4 rows selected

CAST Conversions of Java Data Types

  • CAST conversions of Java data types to other Java data types

    You can cast a Java object to a different Java data type following the same rules as those for an explicit cast in Java. Casting up or widening is always safe. Casting down or narrowing may cause an execution error if a Java ClassCastException is thrown while Cloudscape processes the CAST. A CAST that is always illegal causes an error when the statement is prepared.

  • CAST conversions of Java data types to SQL-J data types

    You can CAST Java objects to SQL-J data types according to the mapping of Java data types to SQL-J data types (see Table 1-13, "Conversion of Java Types to SQL-J Types"). If there is a direct mapping of a Java object to an SQL-J data type, conversions allowed on the SQL-J data type are always allowed on the Java object. For example, you can cast a java.lang.Integer to the same data types to which you can cast an INTEGER.

    -- returns 3.0
    VALUES CAST (NEW java.lang.Integer(3) AS FLOAT)

  • Casting expressions to Java data types

    You can cast a non-NULL expression or dynamic parameter to a Java data type if and only if the expression evaluates to an SQL-J data type that has a direct mapping to the target Java class (see Table 1-12, "Conversion of SQL-J Types to Java Classes During Method Invocation"). For example, the following expression is legal:

    -- 1 evaluates to INTEGER.
    -- INTEGER maps directly to java.lang.INTEGER
    VALUES CAST (1 AS CLASS java.lang.Integer)

    The following expression is not:

    -- 1.0 evaluates to DECIMAL.
    -- DECIMAL does not map directly to java.lang.INTEGER
    VALUES CAST (1.0 AS CLASS java.lang.Integer)

  • NULLs and dynamic parameters

    You can cast NULL and dynamic parameters to any valid Java class.

CAST Examples

-- return only the integer portion of the mileage
SELECT CAST (miles AS INT)
FROM Flights

-- you can cast expressions other than simple column references,
-- and you can use the CAST expression in an aggregate
SELECT AVG(CAST (customized_tour.getTotalCost() AS INTEGER))
FROM customizedtours

-- when a column stores subclasses,
-- you can use the CAST expression to cast to a subclass
SELECT (CAST (
    person AS CLASS JBMSTours.serializabletypes.Child)).getParent()
FROM People
WHERE person INSTANCEOF JBMSTours.serializabletypes.Child

-- convert timestamps to text
INSERT INTO mytable (text_column)
VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100)))

-- you must cast NULL as a data type to use it
SELECT airline
FROM Airlines
UNION ALL
VALUES (CAST (NULL AS CHAR(2)))

-- cast dynamic parameters to specify the correct signature
x.overloadedMethod(CAST (? AS CLASS java.lang.Integer))

-- cast a Java double as a decimal
SELECT CAST (normal_rate AS DECIMAL(5,2))
FROM Hotels

-- cast an INT
VALUES (CAST (12 AS VARCHAR(5)), CAST ('35' AS INT))

ij> -- use a CAST to control the number of digits that appear
ij> SELECT AVG(city.showTemperature())
FROM cities;
SQLCol1 --------------- 69.54545454545& ij> SELECT CAST (AVG(city.showTemperature()) AS DECIMAL(5,2))
FROM Cities;
SQLCol1 -------- 69.54 ij> SELECT CAST (AVG(city.showTemperature()) AS INT)
FROM Cities;
SQLCol1 --------------- 69