converting a decimal 20,4 in db2 to a string

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

Select allOpen in new window

HTH,
DaveSlash

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

Select allOpen in new window

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;

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

> 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.

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

Select allOpen in new window

HTH,
DaveSlash

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

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

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)

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     

Select allOpen in new window

HTH,
DaveSlash

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?

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

Select allOpen in new window

Regards,
     Tomas Helgi