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
 

 

Decimal column value in display format when UNLOAD a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Mon Dec 23, 2013 11:17 pm    Post subject: Decimal column value in display format when UNLOAD a table
Reply with quote

Hi,

We have a table named PRDTAB and its column definition are as below:

PRD_VALUE DECIMAL(8,3) NOT NULL
PRD_DESC CHAR(10) NOT NULL

Now, below are two sample rows in table PRDTAB

0 SALES
25.100 MKT PRD

While unloading this table PRDTAB by using below query in SYSIN card in the unload jcl

SELECT PRD_VALUE
,CHAR('|',1)
,PRD_DESC
,CHAR('|',1)

Output found in unload file looks like below:

.....|SALES |
.....|MKT PRD |

. -> represents values in packed decimal format

Please someone provide me a DB2 query for which unload file value looks like below:

0|SALES|
25.100|MKT PRD|

So, basically I need the query in SYSIN card of unload jcl in such a way that PRD_VALUE is in display format and spaces in right end of PRD_DESC should be eliminated after unloading table PRDTAB.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Mon Dec 23, 2013 11:40 pm    Post subject:
Reply with quote

Use the VARCHAR cast function on your decimal values.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Tue Dec 24, 2013 8:51 am    Post subject:
Reply with quote

Hi Sasanka,

As per Akatsukami, You can use CAST function to convert data_types.
You can use either CHAR or VARCHAR.

Here is an example:
Code:

SELECT CAST(PRD_VALUE AS CHAR(9)) AS PRD_VALUE


Keep in mind that, Since you want in a UNLOAD job, when you use VARCHAR, you would also have the "length" of the value right before the value in the file.
you can use RTRIM to remove the trailing spaces in PRD_DESC. This function also make column type into VARCHAR.
Back to top
View user's profile Send private message
sasanka
Warnings : 1

New User


Joined: 18 Jan 2008
Posts: 34
Location: India

PostPosted: Tue Dec 24, 2013 6:36 pm    Post subject: Reply to: Decimal column value in display format when UNLOAD
Reply with quote

Hi,

If we use RTRIM and CAST as given above then my o/p is:

0.000 | SALES |
25.000 | MKT PRD |

But, I want my output to be as below:

0.000|SALES|
25.000|MKT PRD|

Thanks,
Sasanka
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Tue Dec 24, 2013 6:55 pm    Post subject:
Reply with quote

Whenever you use any function in UNLOAD job that makes column length unequal, it will automatically converted into VARCHAR data_type.
Unload file's value for all VARCHAR data_type would be like,

"length of the value" followed by "Actuall Value".

So when you do use RTRIM, the length of the column is varying so you will automatically will have the "length" before the Value.

if you want exact same file as you expected, you should unload file with fixed length and use any SORT step to customize your file.

Are you going to use this file as input anywhere?

Please let me now if someone has better idea icon_idea.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Dec 26, 2013 1:43 pm    Post subject:
Reply with quote

Code:
UNLOAD ... DELIMITED COLDEL '|'

would pretty much do want you asked
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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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