[top]
[prev]
[next]

Documentation Top
Global Index
Reference Manual
TOC Index
Grammar Index
Developer’s Guide
TOC Index
Tuning Cloudscape
TOC Index
|
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:
- numeric
- exact numeric (TINYINT, SMALLINT, INTEGER, LONGINT, DECIMAL, NUMERIC)
- approximate numeric (FLOAT, REAL, DOUBLE PRECISION)
- string
- date/time
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.
VALUES CAST (X'000' AS boolean);
VALUES CAST (X'001' AS boolean);
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:
INSERT INTO charColumn VALUES 1
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.
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:
SELECT * FROM t1 WHERE intColumn = '1'
SELECT * FROM t1 booleanColumn = charColumn
INSERT INTO t1 (intColumn) VALUES '1'
SELECT 1 + '2' FROM t1
SELECT * FROM t1 WHERE charColumn
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.
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:
VALUES CAST (1 AS CLASS java.lang.Integer)
The following expression is not:
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
SELECT CAST (miles AS INT) FROM Flights
SELECT AVG(CAST (customized_tour.getTotalCost() AS INTEGER)) FROM customizedtours
SELECT (CAST ( person AS CLASS JBMSTours.serializabletypes.Child)).getParent() FROM People WHERE person INSTANCEOF JBMSTours.serializabletypes.Child
INSERT INTO mytable (text_column) VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100)))
SELECT airline FROM Airlines UNION ALL VALUES (CAST (NULL AS CHAR(2)))
x.overloadedMethod(CAST (? AS CLASS java.lang.Integer))
SELECT CAST (normal_rate AS DECIMAL(5,2)) FROM Hotels
VALUES (CAST (12 AS VARCHAR(5)), CAST ('35' AS INT))
ij> 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
|