I have a packed decimal column in the source whose COBOL copybook definition is like
15 DKI-AMT S9(7)V99
There are some spaces coming for some of the records for this column. But my DataStage job which is reading this field as DECIMAL comp-3 is treating those spaces as 4040404040 and loading into the target table. so when i run a query I am getting 4040404.04 for those records which has spaces for this column in the source.
In DataStage CFF file stage, the column definition is :
DKI_AMT DECIMAL(9,2)
Could some one suggest me how to handle this?
Thanks.
Converting spaces in the packed decimal field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Don't allow spaces in a PIC 9 field? That is, make sure that they send you clean data, or declare the field to have an X picture and transform within your DataStage job.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Typically in that case one would need to check the string for spaces and only 'unpack' it when the value is something other than spaces. I don't think the CFF stage has an option to support that, worst case you'll need to do that in a downstream transformer as noted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks Ray & Craig !!
So, in this case, if the source cannot provide me the clean data for this column, do I have to code like below in the transformer?
IF DKI_AMT = 4040404.04 THEN @NULL Else ....
I am asking this because, I have already coded this way and resolved the problem, but not really convinced with it.
Please let me know if you think there is a better way to handle this.
Thanks again !!
So, in this case, if the source cannot provide me the clean data for this column, do I have to code like below in the transformer?
IF DKI_AMT = 4040404.04 THEN @NULL Else ....
I am asking this because, I have already coded this way and resolved the problem, but not really convinced with it.
Please let me know if you think there is a better way to handle this.
Thanks again !!
That could be one way if that works for you. Another would be to leave it as a PIC X field in the CFF and then test for spaces later. If not spaces, use the SDK transform/routine to unpack the real value. I don't have any DataStage access to check on the exact name but those routines are all there if dig down to them in the Manager.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers