Using the CAST Function in Showcase Query

 

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.

How is the CAST function used?

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>)

Examples of the CAST Function

1. You have a character field containing digits called POSTALCODE, which is defined as CHAR, but an application needs this data as a numeric value. Cast the POSTALCODE column to data type integer, as follows.

   CAST(POSTALCODE AS INTEGER)

2. You want to create a column that is a concatenation of a customer account number and their various order numbers, separated by a hyphen. Cast both fields as data type char and concatenate them like so:

   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.  

3. The product ID is stored in the ORDERS table as data type CHAR and in the PRODUCT table as data type INTEGER. The tables must be joined on these fields. Cast one of these fields as the data type of the other. It is usually preferable to cast the numeric value to a character value.  In the case of a Join, this change is made by editing the Join expression from the Tables dialog.

   WHERE ORDERS.PRODUCTID = CAST( PRODUCTS.PRODUCTID AS CHAR( 8 ) )

4. Character coded set identifiers (CCSID) denote the code page used to store character data on the iSeries. You may have character fields that are stored as hex data (CCSID 65535), often the default CCSID of the iSeries. It may be necessary to change them to an EBCDIC CCSID of 37 for use by other applications. Use the CAST function to convert them to the desired CCSID. The length should always be specified to prevent data truncation when doing this type of conversion. No warning message is issued if truncation should occur in this case.

   CAST( LNAME AS CHAR( 15 ) CCSID 37 )

Caveats

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.’

What is the difference between the CAST function and the CONVERT function?

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:

CONVERT(AGE, SQL_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. 

Ref#: 1479025