CAST is a function that converts an argument to a specified target data type. CAST is used when data needs to be returned by a query as a data type that differs from the way in which it is stored in the table. This need might arise for a number of reasons, such as joining two tables where the fields have dissimilar data types or creating strings from numeric data for the purposes of concatenation or comparative conditioning.
If the CAST operand is the keyword NULL, the target data type can be any data type and will result in a null value of the specified data type. This syntax may be particularly useful when using Query to create a result table having a field of the desired data type that is initialized to NULL values.
CAST(NULL AS <data_type>)
If the CAST operand is an expression, the result is the argument value converted to the specified target data type. Allowable target data types are determined by the data type of the cast operand. (For details, see the table “Supported Casts Between Data Types” available in Query or Report Writer Help.)
CAST( AS <data_type>)
CAST(POSTALCODE AS INTEGER)
STRIP(CAST(CUST_ID AS CHAR( 8 ))) || '-' || CAST(ORDNUM AS CHAR(8))
The STRIP function is added to the first expression to prevent trailing spaces from preceding the hyphen. Note: Starting in V5R3, the iSeries will automatically CAST a numeric data type to a character data type when it is used in some string expressions such as a concatenation or when it is compared with a character field.
WHERE ORDERS.PRODUCTID = CAST( PRODUCTS.PRODUCTID AS CHAR( 8 ) )
CAST( LNAME AS CHAR( 15 ) CCSID 37 )
In some cases, the target data type must include a field length specification, and the length can be important. For example, if Field1 is a DECIMAL 15,2 the expression CAST(FIELD1 AS CHAR) will return a SQL0433 error saying that significant digits have been truncated. It’s important that a length be specified that can accommodate the data within the field.
There are limitations on the types of data conversions that are permitted between source and target data types. A table of the types of CAST operations allowed can be found in the Query Help text under ‘Supported Casts between data types.’
CAST is a server function that is supported on OS/400 V4R2 and above. CONVERT is an ODBC function that can be used when you need a portable query to run against data sources other than the iSeries. It returns a converted column, scalar function result, or literal value in the specified data type. For example, to convert each employee’s age from SMALLINT to CHAR:
For more information on the CONVERT FUNCTION see the Help text in Query or refer to the documentation for the specific ODBC driver when using a non-ShowCase data-source.
Still have questions? We can help. Submit a case to Technical Support.