View previous topic :: View next topic
|
Author |
Message |
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
Use the VARCHAR cast function on your decimal values. |
|
Back to top |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
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 |
|
|
sasanka Warnings : 1 New User
Joined: 18 Jan 2008 Posts: 34 Location: India
|
|
|
|
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 |
|
|
sureshpathi10
Active User
Joined: 03 May 2010 Posts: 154 Location: Kuala Lumpur
|
|
|
|
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 |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
UNLOAD ... DELIMITED COLDEL '|' |
would pretty much do want you asked |
|
Back to top |
|
|
|