format of a numeric

Question

I have a problem with the return of my query , I'm using DB2.

Instead of having XX,ZZZ I have XX.ZZZ (eg :12,123 and 12.123 ).Then I have problem in a calclulation inside my main program using the result.

How can I change the numeric format?

Following is the code I am using, and I want to convert "myTable.myAmount" to this format XX,ZZZ

Thank you

select CASE WHEN myTable.myNb>30 
then myTable.myAmount
ELSE '0' END AS PAR30 from myTable

Answer 1

As it was pointed out before, floats do not have a fixed "string" format, they are stored as 16-byte structure in the database which you can examine like this:

db2 "values hex(float(0.1))"

1               
----------------
9A9999999999B93F

How it will be displayed depends purely on the client. E.g. DB2 CLP will rely on the LOCALE in your environment.

Floats by default are displayed in the scientific notation:

db2 "values float(0.1)"

1                       
------------------------
  +1.00000000000000E-001

Decimal field separator is the most obvious with the decimal data type:

db2 "values decimal(0.1,5,1)"

1      
-------
    0.1

Now, I would expect that changing LC_NUMERIC to a locale that uses comma as the separator should affect the result, but it doesn't. For DB2 CLP one needs to change LC_ALL (at least based on my quick test). E.g.:

export LC_ALL="pl_PL.UTF-8"
db2 connect to sample
db2 "values decimal(0.1,5,1)"

1      
-------
    0,1

If you want specific textual format and I don't want Db2 to do the work you can use VARCHAR_FORMAT:

db2 "values varchar_format(decimal(0.1,5,1), '990.999')"

1                                                                                                                                                                                                                                                             
--------------
   0.100       

(or whatever format you want)

Answer 2

Consider the following example:

>db2 "describe select CASE WHEN myTable.myNb>30 then myTable.myAmount ELSE '0' END AS PAR30 from (values (float(1.23), 30)) myTable(myamount, mynb)"

 Column Information

 Number of columns: 1

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 996   DECFLOAT                 16  PAR30                                     5



>db2 "describe select CASE WHEN myTable.myNb>30 then myTable.myAmount ELSE 0 END AS PAR30 from (values (float(1.23), 30)) myTable(myamount, mynb)"

 Column Information

 Number of columns: 1

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 480   DOUBLE                    8  PAR30                                     5

The example shows how to get the column data types of a select statement.
The only difference between 2 statements is using '0' vs 0 in the ELSE clause.
We see, that DB2 uses the DECFLOAT data type in the 1-st case and DOUBLE in the 2-nd one.
This is probably the root cause of the problem: if an application uses some string variable to get a DECFLOAT value (some programming languages / applications may not have an appropriate variable data type for such a database type), then the result depends on local locale / application settings - we may get either . or , sign as a decimal separator.
But, most programing languages / applications do have an appropriate variable data type for the DOUBLE database data type, so, there shouldn't be any problems with it unless one uses a string variable to read such a value still.

Answer 3

So It is solved, I did convertion to int, I don't know why I need to convert a number to a number but it works now.

SELECT
    CASE WHEN myNb > 30 THEN cast(myAmount as INT)
         ELSE '0' END AS PAR30
FROM myTable;

Thanks all