Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Truncating leading Spaces

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Wed Apr 22, 2009 10:56 pm    Post subject: Truncating leading Spaces
Reply with quote

Hi,

I have a table with values like

Total_Amount Paid_Amount
------------------------------------
0.01 0.00
89.50 0.01

When I am doing a unload using utility ADUUMAIN and select with the below statement:

SELECT CHAR(TOTALAMOUNT), CHAR(PAID_AMOUNT) FROM TABLE1 WITH UR;

I got the values as below in dataset:

000000.01 000000.00
000089.50 000000.01

I want the values into dataset same as in table.
0.01 0.00
89.50 0.01

Could any of you please help me out to acheive this.
Back to top
View user's profile Send private message

srikanthkailasam

New User


Joined: 13 Nov 2006
Posts: 24
Location: Bengalooru

PostPosted: Wed Apr 22, 2009 11:02 pm    Post subject:
Reply with quote

Can you give the variable declarations of Total_Amount Paid_Amount?
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Wed Apr 22, 2009 11:06 pm    Post subject:
Reply with quote

Both are of DECIMAL(9,2) datatype.
Back to top
View user's profile Send private message
srikanthkailasam

New User


Joined: 13 Nov 2006
Posts: 24
Location: Bengalooru

PostPosted: Wed Apr 22, 2009 11:51 pm    Post subject:
Reply with quote

Where are you getting the values from??

Total_Amount Paid_Amount
------------------------------------
0.01 0.00
89.50 0.01

Is it from Spufi or QMF??
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Wed Apr 22, 2009 11:58 pm    Post subject:
Reply with quote

These are the actual values stored in Db2 table.

I want it to unload to a dataset and FTP to text file.

If I am not using CHAR conversion leading Zero's are removed when extracted to dataset.

If the value is 0.01 it comes as .01 in dataset ,So I used CHAR conversion.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 23, 2009 1:17 am    Post subject:
Reply with quote

Quote:
These are the actual values stored in Db2 table.


NO!

Read the description of the char function in DB2 manuals!

DEC(9,2) are numeric data items and would be stored with leading zeros.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2009 2:27 am    Post subject:
Reply with quote

Hello,

Quote:
These are the actual values stored in Db2 table.
Why would you believe thie?
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu Apr 23, 2009 2:36 am    Post subject: Reply to: Truncating leading Spaces
Reply with quote

Quote:
Hello,

Quote:
These are the actual values stored in Db2 table.
Why would you believe thie?

Dick, it might be possible as when you do a select on a decimal field, it shows the value without leading zeroes. Even when i browse my table i don’t get to see the leading zeroes.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Thu Apr 23, 2009 2:57 am    Post subject:
Reply with quote

I think the point being made is that SPUFI, QMF, File-Aid for DB2, and any other software might suppress the leading zeros when being displayed, but the DB2 manual indicates that DECIMAL data types are stored in what COBOL refers to as packed decimal (COMP-3) which means leading zeros are present.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Apr 23, 2009 2:59 am    Post subject:
Reply with quote

Hello,

Keep in mind that when you "see" the values via some query, you are not seeing what is stored in the database, but rather a more "people friendly" presentation of the value.

Very much like in cobol when you move a packed-decimal field to an edited output field to get something like $12,345.88-. The currency sign, the comma, the decimal point, and the actual minus sign are not in the data field. They are inseerted for readability by the definition of the output field.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu Apr 23, 2009 3:09 am    Post subject: Reply to: Truncating leading Spaces
Reply with quote

Quote:
Keep in mind that when you "see" the values via some query, you are not seeing what is stored in the database, but rather a more "people friendly" presentation of the value.
That is true. Therefore i used
Quote:
it might be possible as when you do a select on a decimal field, it shows the value without leading zeroes. Even when i browse my table i don’t get to see the leading zeroes.
and as Terry indicated in SPUFI we get to see the results with suppressed zeroes.
I believe Krishnadeva is worried all about representation and not how the value is being actually stored.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Remove intermediate spaces Pallavi Vishwanath SYNCSORT 1 Fri Jan 15, 2016 2:45 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us