CODEWITHSUNDEEP
sqldb2
mahesh
mahesh

Reputation: 51

in db2 I am exporting the data price value getting like this "+00000000000000000000000020.96000" price value we have decimal column

In db2 when I exporting the data price value, I am getting like this

"+00000000000000000000000020.96000"

how to remove the before zeros of price value.

I am excepting like this "20.96000".

My Query like this 

sum(orders.TOTALPRODUCT) this value I am getting like this "+00000000000000000000000020.96000"

db2 "EXPORT TO customerpurchase2016.csv OF del modified BY coldel, 
SELECT   userreg.logonid, 
         nvl(address.firstname,'') 
                  ||' ' 
                  ||                                                                          address.lastname,
         sum(orders.totalproduct)                                                             AS totalproduct,
         sum(orders.totalproduct+orders.totaltax+orders.totalshipping+orders.totaladjustment) AS totalamount,
         userdemo.field7 
FROM     orders, 
         userreg, 
         userdemo, 
         address 
WHERE    address.member_id=userdemo.users_id 
AND      userdemo.users_id=userreg.users_id 
AND      userreg.users_id=orders.member_id 
AND      orders.status NOT IN('J', 
                              'P', 
                              'X') 
AND      orders.lastupdate BETWEEN '2016-01-01-00.00.00.000000' AND      '2016-12-30-12.00.00.000000' 
AND      address.status='P' 
AND      address.selfaddress=1 
GROUP BY userreg.logonid, 
         userdemo.field7, 
         address.firstname, 
         address.lastname"

Upvotes: 0

Views: 1185

Answers (3)

radicarl
radicarl

Reputation: 327

you can tell the export-script to remove leading zeros in all columns be simply passing striplzeros as argument.

db2 "EXPORT TO customerpurchase2016.csv OF del modified BY coldel, striplzeros
SELECT   userreg.logonid, 
         nvl(address.firstname,'') 
...

See also https://www.ibm.com/support/knowledgecenter/de/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html#r0008303__d73620e525 for more options

Upvotes: 0

CRPence
CRPence

Reputation: 1259

I can not test, but try using cast( sum(orders.TOTALPRODUCT) as decimal( 31, 6 ) in place of the sum(orders.TOTALPRODUCT) from the OP. And if leading zeroes persist, then likely the db2 being used is LUW [no mention of platform nor version info was made in the OP nor tags] and an issue may exist for [both explicit CHAR and possibly also the] implicit casting from numeric to character, whereby leading zeroes are known to be the result of a past defect; i.e. an older database may need modification to enable the casting to function without the leading zeroes per Notes in DB2 for Linux UNIX and Windows 10.1.0->Database fundamentals->SQL->Built-in functions->Scalar functions->CHAR

Decimal to character and leading zeros: In versions previous to version 9.7, the result for decimal input to this function includes leading zeros and a trailing decimal character. The database configuration parameter dec_to_char_fmt can be set to "V95" to have this function return the version 9.5 result for decimal input. The default value of dec_to_char_fmt for new databases is "NEW", which has this function return results which match the SQL standard casting rules and is consistent with results from the VARCHAR function.

Upvotes: 1

Mustafa DOGRU
Mustafa DOGRU

Reputation: 4112

you can use VARCHAR or TRIM OR STRIP functions when exporting table as below; 

db2 "EXPORT TO customerpurchase2016.csv OF del modified BY coldel, 
SELECT   userreg.logonid, 
         nvl(address.firstname,'') 
                  ||' ' 
                  ||                                                                          address.lastname,
         TRIM(sum(orders.totalproduct))                                                             AS totalproduct,
         sum(orders.totalproduct+orders.totaltax+orders.totalshipping+orders.totaladjustment) AS totalamount,
         userdemo.field7 
FROM     orders, 
         userreg, 
         userdemo, 
         address 
WHERE    address.member_id=userdemo.users_id 
AND      userdemo.users_id=userreg.users_id 
AND      userreg.users_id=orders.member_id 
AND      orders.status NOT IN('J', 
                              'P', 
                              'X') 
AND      orders.lastupdate BETWEEN '2016-01-01-00.00.00.000000' AND      '2016-12-30-12.00.00.000000' 
AND      address.status='P' 
AND      address.selfaddress=1 
GROUP BY userreg.logonid, 
         userdemo.field7, 
         address.firstname, 
         address.lastname"

Example;

A sample table:

create table pricetable (price decimal(31,5));

insert into pricetable (price) values(20.96);
insert into pricetable (price) values(21.97);
insert into pricetable (price) values(22.98);
insert into pricetable (price) values(20.99);

Exporting without functions;

CALL SYSPROC.ADMIN_CMD( 'EXPORT TO "/tmp/export" OF DEL MESSAGES ON SERVER SELECT PRICE FROM PRICETABLE' );

user@host:/tmp:>cat export 
+00000000000000000000000020.96000
+00000000000000000000000021.97000
+00000000000000000000000022.98000
+00000000000000000000000020.99000

Exporting using varchar or trim or strip functions; 

 CALL SYSPROC.ADMIN_CMD( 'EXPORT TO "/tmp/export" OF DEL MESSAGES ON SERVER SELECT VARCHAR(PRICE) FROM PRICETABLE' );

user@host:/tmp:>cat export
"20.96000"
"21.97000"
"22.98000"
"20.99000"

Upvotes: 0

Related Questions