|
View previous topic :: View next topic
|
| Author |
Message |
kmk2mani
New User
Joined: 08 Jul 2010
Posts: 12
Location: United Kingdom
|
|
|
|
Hi,
I am working on unloading a table where a column is defined as smallint and I would like to have a unloaded value leading zero.
Example:
Column "Division" is defined as smallint and it has got the value as "1" While unloading I would like to have "01" in the unload file. I have tried with CAST but did not help.
Any help on this is much appreciated. |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Can you try DIGITS? |
|
| Back to top |
|
|
kmk2mani
New User
Joined: 08 Jul 2010
Posts: 12
Location: United Kingdom
|
|
|
|
Hi Pandora-Box,
I have just tried DIGITS but did give an error.
SQLCODE = -104, ERROR: ILLEGAL SYMBOL "DIGITS". |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
What happens if you unload this column preceded by a literal zero (assuming the value always has a length of one). |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| How about posting your code to let people see if you coded it correctly? Well, obviously you didn't or it would not have failed but another pair of eyes may spot your mistake. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Nic Clouston wrote: |
| How about posting your code to let people see if you coded it correctly? Well, obviously you didn't or it would not have failed but another pair of eyes may spot your mistake. |
And as much if not more to the point, what is the TS using to unload the table: IBM utility, BMC utility, DSNTIAD, some RYO program? |
|
| Back to top |
|
|
kmk2mani
New User
Joined: 08 Jul 2010
Posts: 12
Location: United Kingdom
|
|
|
|
Dick,
The column will have 2 digits also.
Nic and Akatsukami,
I am using DB2UNLD to unload the table. Please find below the unload query.
UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
OPTIONS NULLID NO NULL OFF
SELECT CAST(CHAR(' ') AS CHAR(01)),
CAST(STORE_NBR AS CHAR(04)),
CAST(BASE_DIV_NBR AS CHAR(02)) ------> Column BASE_DIV_NBR is defined as smallint. |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Can you show us how did you used digits function in the unload card? |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| kmk2mani wrote: |
I am using DB2UNLD to unload the table. Please find below the unload query.
UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
OPTIONS NULLID NO NULL OFF
SELECT CAST(CHAR(' ') AS CHAR(01)),
CAST(STORE_NBR AS CHAR(04)),
CAST(BASE_DIV_NBR AS CHAR(02)) ------> Column BASE_DIV_NBR is defined as smallint. |
"DB2UNLD" is probably the name of a proc executing the IBM DB2 utility. Unfortunately, I don't know of a way to get what you want. Specifying the field EXTERNAL (n) FILL YES would give you a sign prior to leading zeroes. You may have to do some post-processing with *Sort. |
|
| Back to top |
|
|
|
|
