Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# converting a decimal 20,4 in db2 to a string

Posted on 2012-08-27
Medium Priority
5,163 Views
Hi,

I need to convert a decimal 20,4 to a string. How do I do this so that I dont retain any leading zeros or additional padding of zeros in decimal place?
0
Question by:LuckyLucks
• 5
• 5
• 2
• +1

LVL 18

Expert Comment

ID: 38337533
This works for me:

``````create table deleteme (
)

insert into deleteme
values(12345.67890)

from deleteme

12,345.6789   12345.6789              12345.6789
``````

HTH,
DaveSlash
0

LVL 18

Expert Comment

ID: 38337556
I even tried it with a datatype of DECIMAL, and it worked the same.

``````create table deleteme2 (
)

insert into deleteme2
values(12345.67890)

from deleteme2

12,345.6789   12345.6789              12345.6789
``````
0

Author Comment

ID: 38337588
I am doing this as a DB2 pass thru SQL in SAS:

proc sql;
connect to db2 as connection1 (database=mydb2db USER=a USING=pswd) ;

create table WORK.SASTABLE1  as

select * from connection to connection1 (

SELECT t1.ID,

SUM(t1.OUTSTNDNG_AMT) AS OUTSTNDNG_AMT_OLD ,

CAST(CHAR(SUM(t1.OUTSTNDNG_AMT)) as VARCHAR(60)) AS OUTSTNDNG_AMT

FROM SCHEMA1.TABLE1 t1
GROUP BY t1.ID
);

disconnect from connection1;

quit;
0

Author Comment

ID: 38337953
A few things - the columns is a decimal (20,4) not a numeric 20,4 in db2
Secondly, I cannot use VARCHAR(columnName) since my version of db2 doesnt have a rountine VARCHAR. I am using v9.1
0

LVL 18

Expert Comment

ID: 38338381
> the columns is a decimal (20,4) not a numeric 20,4 in db2

Good thing it works the same for decimal. (see above)

> I cannot use VARCHAR(columnName) since my version of
> db2 doesnt have a rountine VARCHAR.

Good thing CHAR worked equivalently.
0

LVL 18

Expert Comment

ID: 38338433
If you do want a VARCHAR, you could probably use CAST.

``````select aDec,
from deleteme2

12,345.6789   12345.6789              12345.6789              12345.6789
``````

HTH,
DaveSlash
0

Author Comment

ID: 38339204
Dave, whats the version of DB2 you are using and also db2 client? In my case, it pads with zero when casting.
0

LVL 46

Expert Comment

ID: 38339329
Hi Lucky,

Everything that Dave has suggest is spot on.

select * from connection to connection1 (

What kind of system is on the other end of connection1?  The local DB2 system will not prepend the leading zeros.  Since the column is recast, the local system has no idea what the original data type was or how many leading zeros are applicable.

Knowing what kind of DBMS the remote system is using will go a long way to solving this.  The SQL will need to be tailored to that server.

Kent
0

Author Comment

ID: 38341685
I am running my sas code (that involves a db2 pass thru SQL) on a SAS AIX server. The SAS server has a db2 client installed v9.1 and connects to a DB2 db server v9.1 fix 5.

I have also tried for test purposes using a Query Tool - AQT - to talk to DB2 and face the same padding issues.

When I do
SELECT CUST_AMT, CAST(CUST_AMT as VARCHAR(100)) AS NEW
FROM SCHEMA1.TABLE1 t1

42846(-461)[IBM][CLI Driver][DB2/AIX64] SQL0461N  A value with data type "SYSIBM.DECIMAL" cannot be CAST to type "SYSIBM.VARCHAR".  SQLSTATE=42846

So, I tried:

SELECT CUST_AMT, CAST(CHAR(CUST_AMT) as VARCHAR(100)) AS NEW
FROM SCHEMA1.TABLE1 t1

And I end up with padded zeros. I read somewhere that I should use VARCHAR(col) instead of CHAR(col) but this routine doesnt exist

42884(-440)[IBM][CLI Driver][DB2/AIX64] SQL0440N  No authorized routine named "VARCHAR" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884
(0.03 secs)
0

LVL 18

Expert Comment

ID: 38342263
It's a bit of a kludge, but I suppose you could manually trim off the zeros.

e.g.
``````select nsav,
trim(l '0' from nsav) as trimmed
from deleteme

NSAV        TRIMMED
001.2345    1.2345
123.4567    123.4567
00001.2     1.2
``````

HTH,
DaveSlash
0

Author Comment

ID: 38342606
I am doing something like this: CHAR(INTEGER(AMT)) || '.' || SUBSTR(DIGITS(AMT),18,4)

The first portion takes the right side of decimal and makes it as string w/o padding
The third portion pads out the number to its full length and then cuts out the right side after decimal.

The only problem is a number like -0.09 becomes 0.09 losing its minus as INTEGER makes it 0 and not -0. ANy ideas how to fix this?
0

LVL 46

Accepted Solution

Kent Olsen earned 2000 total points
ID: 38343227
Hi Lucky,

Can you describe your topography a bit more.  I know that you're running SAS and that there's at least 1 UDB/LUW system running on AIX, but that's not much to go on.

UDB/LUW trims the leading zeros, so they're coming from some place else.  Knowing a bit more about your layout may help.

Kent

Regarding your last question, you could prepend a '-' when necessary, but there really should be a better way.

case when amt < 0 then '-' else '' end || CHAR(INTEGER(AMT)) || '.' || RIGHT(DIGITS(AMT),4)
0

LVL 26

Expert Comment

ID: 38349763
Hi!

Use STRIP to remove leading and trailing zeros

``````create table deleteme2 (
)

insert into deleteme2
values(12345.67890)

from deleteme2
``````

Regards,
Tomas Helgi
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm nā¦
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of thā¦
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. ā¦
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, youā¦
###### Suggested Courses
Course of the Month12 days, 9 hours left to enroll