ALLBASE/SQL Reference Manual (36216-90216)

Chapter 8 241
Expressions
CAST Function
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;