IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Decimal column value in display format when UNLOAD a table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1788
Location: Bloomington, IL

PostPosted: Mon Dec 23, 2013 11:40 pm
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
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
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
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: 1281
Location: Belgium

PostPosted: Thu Dec 26, 2013 1:43 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top