HP 3000 Manuals HP 3000 Manuals
CAST Function
The CAST function converts data from one data type to another. The CAST
function can be used anywhere a general expression is allowed. CAST is
supported inside functions that support expressions including aggregate
functions. CAST also takes general expressions including nested
functions as input.
Scope
SQL Data Manipulation Statements
SQL Syntax
{CAST ({Expression} {AS} DataType[,FormatSpec])}
{ {NULL } {, } }
Parameters
Expression is the value to be converted. See the
"Expression" section in this chapter for details
on the syntax.
DataType ALLBASE/SQL data type: CHAR(n), VARCHAR(n),
DECIMAL(p[,s]), FLOAT, REAL, INTEGER, SMALLINT,
DATE, TIME, DATETIME, INTERVAL, BINARY(n),
VARBINARY(n), TID.
The LONG BINARY(n) and LONG VARBINARY(n) cannot
be used in the CAST operations.
FormatSpec Format specification used for DATE, TIME,
DATETIME, INTERVAL conversions. FormatSpec is
the same as that used in the date/time conversion
functions.
Description
The following table shows what data type conversions the CAST function
supports. These are the status codes used in the table:
* Y--is supported
* N--is not supported
* E--is an ALLBASE/SQL Extension (not a part of ANSI standard)
Table 8-1. Data Types for CAST Function
---------------------------------------------------------------------------------------------------
| | |
| Source | Target Data Type |
| Data Type | |
| | |
---------------------------------------------------------------------------------------------------
| | |
| | EN AN(2) VC CHAR(n) B VB DATE TIME DT I TID |
| | (1) -- |
| | -- |
| | |
| EN (1) | Y(3) Y (3) Y(4) Y(4) E (4) E (4) N N N N N |
| | |
| | |
| AN (2) | Y (3) Y(3) Y(4) Y (4) E (4) E (4) N N N N N |
| | |
| | |
| VARCHAR(n) | Y (4) Y(4) Y(3) Y(3) Y (3) Y (3) Y (3) Y (3) Y (3) Y(3) E(4) |
| | |
| | |
| CHAR(n) | Y (4) Y(4) Y (3) Y (3) Y (3) Y (3) Y (3) Y (3) Y (3) Y (3) E(4) |
| | |
| | |
| BINARY | E (4) E (4) Y (3) Y(3) Y (3) Y (3) E(4) E (4) E (4) E (4) E(4) |
| | |
| | |
| VARBINARY(n) | E (4) E (4) Y(3) Y(3) Y (3) Y (3) E (4) E (4) E(4) E (4) E(4) |
| | |
| | |
| DATE | E (3) E(3) Y(3) Y (3) E (4) E(4) Y(3) N N N N |
| | |
| | |
| TIME | E (3) E (3) Y (3) Y (3) E (4) E (4) N Y (3) N N N |
| | |
| | |
| DATETIME | E (3) E(3) Y(3) Y (3) E (4) E (4) N N Y (3) N N |
| | |
| | |
| INTERVAL | Y (3) E (3) Y (3) Y(3) E (4) E (4) N N N Y (3) N |
| | |
| | |
| TID | N N E (4) E (4) E (4) E(4) N N N N Y(3) |
| | |
| | |
---------------------------------------------------------------------------------------------------
(1) ENExact Numeric (SMALLINT, INT[EGER], DEC[IMAL][(p[,s])],
NUMERIC[(p[,s])])
(2) ANApproximate Numeric (FLOAT[(p)] or DOUBLE PRECISION, REAL)
(3) Implicit conversion also supported
(4) Conversion supported only with CAST
* If input to CAST is NULL, then the result of the CAST operation is
NULL.
* ALLBASE/SQL supports implicit data conversion between:
* Numeric data types to numeric data types
* Character data types to character data types
* Binary data types to binary data types
* Binary data types to character data types
* Character data types to binary data types
When CAST is used to do these conversions, all existing rules are
applied.
* When a number is converted, if the number does not fit within the
target precision, an overflow error occurs.
* When converting from an approximate numeric to an exact numeric or
from an exact numeric to an exact numeric with less scale
(integers have a scale of 0), the extra digits of scale beyond the
target scale are dropped without rounding the result.
* If both source and target data type are character strings, the
language of the result string is the same as the source.
* If the source data type is a character string and the target data
type is a numeric, then the source value must only contain a
character representation of a number. The result of the
conversion is the numeric value that string represented.
If the source value is not a numeric string, an error occurs.
* If the target data type is CHAR(n), and the source data type is an
exact numeric, the result is a character representation of that
exact numeric. If the source value is less than zero, the first
character of the result is a minus sign. Otherwise, the first
character is a number or a decimal point.
If the length of the resulted string is less than n, then blanks
are added on the right. If the length of the resulted string is
greater than n, an error occurs. The same algorithm applies if
the target data type is VARCHAR(n), except that there is no need
to pad the numeric string if its length is less than n.
* If the target data type is CHAR(n) and the source data type is an
approximate numeric, then the number is converted to a character
representation in scientific notation.
If the length of the resulted string is less than n, then blanks
are added on the right. If the length of the resulted string is
greater than n, then an error occurs. The same algorithm applies
if the target data type is VARCHAR(n), except that there is no
need to pad the numeric string if its length is less than n.
* Conversion between character and binary data types is supported
implicity as well as with CAST. The same rules still apply with
CAST. If a target is shorter than the source, truncation occurs.
If the target is larger than the source, the target is zero-filled
in the case of BINARY(n), and blank-filled in the case of CHAR(n).
* When converting a non-character data type to BINARY(n) or
VARBINARY(n), the data is not modified. Only the type changes so
that the data is treated as binary data. The size of the source
and the target in bytes must be equal in the case of BINARY(n),
and the size of the source must be less than or equal to the size
of the target in the case of VARBINARY(n). Otherwise, an error
occurs.
For decimal numbers, each digit of precision contributes 4 bits
and 4 bits for the sign. The overall size is rounded up to a
4-byte boundary. The storage size for DATE, TIME, DATETIME, and
INTERVAL is 16 bytes.
* When converting from BINARY(n) or VARBINARY(n) into a
non-character data type, the data is not modified. Only the type
changes so that the data is treated as a number of the target data
type. The actual size of the source and the target in bytes must
be equal, or an error occurs.
* Conversion between binary data types and numeric data types is an
ALLBASE extension and is not allowed according to the ANSI SQL2
standard.
* Converting a character string to a DATE, TIME, DATETIME or
INTERVAL with CAST is equivalent to using the respective date/time
function, TO_DATE, TO_TIME, TO_DATETIME, or TO_INTERVAL. All the
same rules apply.
* Using CAST to convert numeric types directly to date/time types is
not allowed. This should be done by nesting the CAST functions so
that the numeric value is first converted to a character string,
and then converted to the date/time data type.
* Converting a date/time data type to:
* A character type with CAST is equivalent to using the
TO_CHAR date/time function. All the same rules apply.
* An INTEGER is equivalent to using the TO_INTEGER date/time
function. This function converts date/time column value
into an INTEGER value which represents a portion of the
date/time column. If the source data type of CAST is
date/time data type, and the target data type is INTEGER,
all rules for TO_INTEGER to convert date/time into INTEGER
will be applied. The FormatSpec must be used to specify a
single component of the date/time data type (i.e. HH, MM,
SS, DAYS, etc.).
* Other numeric types are also allowed using CAST. In this
case, the date/time data type is first converted to an
INTEGER applying all the TO_INTEGER rules, then is
converted from INTEGER to the target data type.
Examples
1. You will see the result has VendorNumber presented as:
Vendor9000, Vendor9020,....
CREATE TABLE PurchDB.SupplyPrice
( PartNumber CHAR(16) NOT CASE SENSITIVE not null unique,
VendorNumber INTEGER,
VendPartNumber CHAR(16) lang=german NOT CASE SENSITIVE,
UnitPrice DECIMAL(10,2),
DeliveryDays CHAR(2),
DiscountQty SMALLINT)
SELECT PartNumber, 'Vendor' || CAST(VendorNumber AS VARCHAR(4))
FROM PurchDB.SupplyPrice
WHERE VendorNumber BETWEEN 9000 AND 9020;
2. You will see the INTERVAL constant shown as: 0 23:00:00:000
SELECT PartNumber, CAST(CAST(23,CHAR(2)),INTERVAL,'HH')
FROM PurchDB.SupplyPrice;
3. You will see the INTEGER constant shown as: 99
SELECT PartNumber, CAST('9999-12-31',INTEGER,'CC')
FROM PurchDB.SupplyPrice;
4. SELECT SUM with CAST
SELECT SUM(CAST(DeliveryDays, SMALLINT))
FROM PurchDB.SupplyPrice
WHERE VendorNumber BETWEEN 9000 AND 9020;
5. EXEC SQL with CAST
EXEC SQL begin declare section;
char hostvar1[16];
sqlbinary hostvar2[8];
EXEC SQL end declare section;
Assume there is only one row qualified for the following query.
EXEC SQL select PartNumber, CAST(UnitPrice,BINARY(8))
INTO :hostvar1, :hostvar2
FROM PurchDB.SupplyPrice
WHERE VendorNumber BETWEEN 9000 AND 9020;
6. You will see the DECIMAL constant shown as: 99.99
SELECT PartNumber, CAST(99.99,VARCHAR(10))
FROM PurchDB.SupplyPrice;
[REV END]