SQL / DB2: Conversion from number to CHAR with fixed number of positions, leading zeros and one decimal?

[guvoko]

SQL / DB2: Conversion from number to CHAR with fixed number of positions, leading zeros and one decimal?

Hello! As a beginner in DB2, I need some help in "translating" a part of a query. In Oracle it looks like:

select a.employee_number||' '||
to_char(sum(a.ot_hours),'00000009.9')||' '||
to_char(sum(a.amount),'000000000000009.99')||' '||
to_char(sum(a.meals),'000000000000009.99') record
from...

Which returns records like:
90009999 00000009.5 000000000000756.04 000000000000015.00

We would like to run the same query - generating exactly the same format including length, leading zeros and decimals - in JDE one World XE / DB 2 (using iSeries Access for Web).

With some help I got as far as:

select c.employee_number||' '||substr('0000000000',1,10-char_length(rtrim(char(sum(c.ot_hours)))))||rtrim(char(sum(c.ot_hours)))||' '||substr('000000000000000000',1,18-char_length(rtrim(char(sum(c.amount)))))||rtrim(
(char(sum(c.amount))))||' '||substr('000000000000000000',1,18-char_length(rtrim(char(sum(c.meals)))) )||rtrim(char(sum(c.meals))) "record"
from..

which returns records like:
90009999 0000009.50 000000000000756.04 000000000000015.00

It is already pretty much the same except for the second value which has 2 decimals instead of one. So what we need is:

00000009.5
instead of:
0000009.50

Any help is highly appreciated.

Best regards,
Gudrun