DB2 Version 9.7 for Linux, UNIX, and Windows

CHAR(`decimal-expression`) scalar function return behavior has been changed

In databases created in Version 9.7, leading zeros and a trailing decimal character are removed from the result of the CHAR scalar function (decimal to character). This behavior also applies to the CAST specification from decimal to character.

Details

In previous releases, the CHAR scalar function (decimal to character) as well as the CAST specification from decimal to character returns leading zeros and a trailing decimal character in the result. The behavior is inconsistent with the VARCHAR scalar function as well as the SQL standard casting rules.

The following examples illustrate the changed behavior:

Example 1

Assuming that you create the following table and data:

CREATE TABLE MY_TAB (C1 DEC(31,2)  
INSERT INTO MY_TAB VALUES 0.20, 0.02, 1.20, 333.44

When you issue the following statement:

SELECT CHAR(C1)FROM MY_TAB

In previous releases, the following result set is returned:

1                                 
--------------------------------- 
00000000000000000000000000000.20  
00000000000000000000000000000.02  
00000000000000000000000000001.20  
00000000000000000000000000333.44

In Version 9.7, the following result set is returned:

1                                 
---------------------------------
.20
.02
1.20
333.44

Example 2

Assuming that you create the following table and data:

CREATE TABLE MY_TAB (C1 DEC(5,0))  
INSERT INTO MY_TAB VALUES 1, 4.0  
SELECT CHAR(C1)FROM MY_TAB

When you issue the following statement:

SELECT CHAR(C1)FROM MY_TAB

In previous releases, the following result set is returned:

1
-----
0001.
0004.

In Version 9.7, the following result set is returned:

1
-----
1
4

User response

If previous release semantics are required, you can use the following methods:

Update the specific SQL statements to use the CHAR_OLD(<decimal>) scalar function rather than the CHAR(<decimal>) scalar function.
Update the database configuration parameter dec_to_char_fmt to 'V95'. After setting the database configuration parameter, SQL statements using the CHAR scalar function or the CAST specification from decimal to character will need to be recompiled. For static SQL, you must rebind the package. For dynamic SQL, the statement simply needs to be invoked.

If you want migrated databases to use the new format, set the dec_to_char_fmt to 'NEW'.

CHAR(decimal-expression) scalar function return behavior has been changed

Details

User response

CHAR(`decimal-expression`) scalar function return behavior has been changed