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
13
Medium Priority
?
5,163 Views
Last Modified: 2012-09-15
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
Comment
Question by:LuckyLucks
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 38337533
This works for me:

create table deleteme (
  aDec numeric(20,4)         
)

insert into deleteme
values(12345.67890)

select aDec,
       char(aDec) as aChar,
       varchar(aDec) as aVarchar
  from deleteme

       ADEC   ACHAR                   AVARCHAR  
12,345.6789   12345.6789              12345.6789

Open in new window


HTH,
DaveSlash
0
 
LVL 18

Expert Comment

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

create table deleteme2 (
  aDec decimal(20,4)          
)

insert into deleteme2
values(12345.67890)

select aDec,                    
       char(aDec) as aChar,     
       varchar(aDec) as aVarchar
  from deleteme2

       ADEC   ACHAR                   AVARCHAR  
12,345.6789   12345.6789              12345.6789

Open in new window

0
 

Author Comment

by:LuckyLucks
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:LuckyLucks
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

by:Dave Ford
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

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

select aDec,
       char(aDec) as aChar,
       varchar(aDec) as aVarchar,
       cast(aDec as varchar(20)) as casted
  from deleteme2

       ADEC   ACHAR                   AVARCHAR                CASTED    
12,345.6789   12345.6789              12345.6789              12345.6789

Open in new window


HTH,
DaveSlash
0
 

Author Comment

by:LuckyLucks
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

by:Kent Olsen
ID: 38339329
Hi Lucky,

Everything that Dave has suggest is spot on.

In your query:

   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

by:LuckyLucks
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

by:Dave Ford
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     

Open in new window


HTH,
DaveSlash
0
 

Author Comment

by:LuckyLucks
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

by:
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

by:Tomas Helgi Johannsson
ID: 38349763
Hi!

Use STRIP to remove leading and trailing zeros
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023197.htm

create table deleteme2 (
  aDec decimal(20,4)          
)

insert into deleteme2
values(12345.67890)

select aDec,                    
       strip(char(aDec), B, '0') as aChar,     
       strip(varchar(aDec), B, '0')  as aVarchar
  from deleteme2

Open in new window


Regards,
     Tomas Helgi
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question