Converting spaces in the packed decimal field

dsx999 · Post by **dsx999** » Mon Mar 14, 2011 8:50 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 14, 2011 1:22 pm

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.

chulett · Post by **chulett** » Mon Mar 14, 2011 5:41 pm

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.

dsx999 · Post by **dsx999** » Thu Mar 17, 2011 3:44 am

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 !!

chulett · Post by **chulett** » Thu Mar 17, 2011 6:33 am

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.