Varchar to decimal transformation in generic RCP job

udayanguha · Post by **udayanguha** » Thu Oct 01, 2015 8:24 am

Hi,
I have a generic job which is loading data from sequential file to a database.
Sequential_File --> Copy stage --> DB2 connector stage.
I have some decimal fields in the sequential file which are blank and I when I try reading the file (through schema file), it gives me the following error;
Field has import error and no default value.
I read at several forums that I need to read blank decimal fields as Varchar and then convert them into decimal fields during transformation.
Now my doubt is can I handle such string to decimal conversion in a generic job which is using RCP since I am not mentioning any columns in the stage metadata.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 01, 2015 5:55 pm

You could try using a Modify stage. Every specification in a Modify stage can be a job parameter.

udayanguha · Post by **udayanguha** » Thu Oct 01, 2015 10:45 pm

Hi Ray,
Since I am using RCP, I don't even know any of the field names. In that case, how can I use the modify stage to handle any decimal field? Would be really helpful if you can elaborate a bit.

ArndW · Post by **ArndW** » Fri Oct 02, 2015 7:41 am

Doesn't your schema file contain the column names? The name of the column could be passed to the job as a parameter, and then that string is used in a modify stage to create an appropriate column.

udayanguha · Post by **udayanguha** » Fri Oct 02, 2015 9:30 am

Hi Andrew,
I have multiple files and there are too many fields like field1 in file1, field2 in file 2 so there's no common fieldname in the files. Every file has its own fields and its own field names.
In this case would I be able to handle the transformation through a generic job?

Mike · Post by **Mike** » Fri Oct 02, 2015 4:20 pm

Create yourself a little utility using your favorite tool ksh, perl, server job, parallel job, BASIC routine, or whatever.

Your utility will read through the schema file looking for decimal columns and generate all of modify specifications that you need.

Then just pass all of the modify specifications into your generic job as a job parameter.

Mike

cdp · Post by **cdp** » Mon Oct 05, 2015 8:56 pm

What if you just try adding a Null field value equal to '' in your Sequential file Stage ? (Format tab>Field defaults>Null Field value)

udayanguha · Post by **udayanguha** » Mon Oct 05, 2015 10:52 pm

I tried adding a NULL value in the schema file itself and that worked. Thanks all.