The environmentis udb 8.1 fixpack 4 on aix 5.2

One of the app personal gave me this sql

u060esp1:/tmp>cat 1.sql
select distinct rtrim(char(reg_msu_qy))
concat ' for ' concat char(reg_rtl_prc_am) as test
from esp.adv_grp_prm_prc where reg_rtl_prc_am=2.990;

u060esp1:/tmp>db2 -tf 1.sql

TEST
---------------------
1 for 00002.990
2 for 00002.990

2 record(s) selected.

The app personal asked me this, how to change ths sql to get rid of
the leading zeros means she wants to see the output like this.
TEST
---------------------
1 for 2.990
2 for 2.990

Please note one point here, the value 2.990 is shown only for sample, in the table there may be several values like 13.990 or 123.449 etc.,

Actually I suggested like this,
select distinct rtrim(char(reg_msu_qy)) concat ' for '
concat substr(char(reg_rtl_prc_am),5,6) as test
from esp.adv_grp_prm_prc
where reg_rtl_prc_am=2.990;
But it works only for values like 2.990 or 1.990 or 4.990 but my answer is not generic one to ger rid of leading zeros.

If somebody share there knowledge to get rid of leading zeros in udb,it will be grate.

Thanks
Jagadish

To further your example
.... ' for ' concat RTRIM(char(INTEGER(SUBSTR(char(reg_rtl_prc_am),1,P OSSTR(char(reg_rtl_prc_am),'.') - 1)))) concat SUBSTR(char(reg_rtl_prc_am),POSSTR(char(reg_rtl_pr c_am),'.')) )

Really it is grate, Your sql is producing what I want, I am just pasting what I ran in our aix box.

cat 20.sql

select distinct rtrim(char(reg_msu_qy)) concat ' for '
concat RTRIM(char(INTEGER(SUBSTR(char(reg_rtl_prc_am),1,P OSSTR(char(reg_
rtl_prc_am),'.') - 1)))) concat SUBSTR(char(reg_rtl_prc_am),POSSTR(char(reg_rtl_
prc_am),'.'))

from esp.adv_grp_prm_prc ;

1 for 100.000
1 for 11.000
1 for 11.090
1 for 11.112
1 for 11.270

If you donot mind, can you explain the logic little bit. If you are busy just leave it, I will read the documentation about the functions whatever you used in the udb manuals.

Thanks
Jagadish

The addition of POSSTR (or the equivalent LOCATE) to find the position of the decimal. So with the first SUBSTR, it locates everything to the left of decimal. You have to subtract 1 from the result of POSSTR as it specifies the positon of the decimal. So the 2nd SUBSTR takes everything to the right of the decimal.