Leading & trailing zero's in column export stage

Aquilis · Post by **Aquilis** » Sat Mar 26, 2011 7:32 am

Hi
I am capturing database rejections by adding reject link to oracle connector stage. At the other end am restructuring the same rejects as tab separated values through Column Export stage and writing them into a single column. My Job is RCP enabled. Since few columns have Decimal datatype in the Oracle database and upon any database rejections, decimal values are getting rejected with leading & trailing zero's into it.

Is there any property in Column Export stage which removes leading & trailing zeros and put's only actual decimal value. currently using Default decimal precision & scale [38,10]

eg:
If am inserting any value "44.00" into database, and rejection happens due to key violation , am getting value as
"0000000000000000000000000044.0000000000".

Can any body help.

abhilashnair · Post by **abhilashnair** » Sat Mar 26, 2011 7:53 am

If you have a sequential file, which is storing your rejects, then DataStage will always add leading zeroes to decimal values.

You may need to have a transformer before the column export stage and then convert the decimal value input into string and pass on the output as Varchar data type. The function to be used in transformer is

DecimalToString(YourColumn,"fix_zero suppress_zero")

Aquilis · Post by **Aquilis** » Sat Mar 26, 2011 7:56 am

My Job is RCP Enabled. I don't have a defined metadata before Column export stage. !!!

ray.wurlod · Post by **ray.wurlod** » Sat Mar 26, 2011 4:53 pm

Don't worry about it. That's now the Decimal number is displayed. It's not stored like that in a database table; it's stored in a binary format. The display format will be used if the decimal is converted to a string (e.g. for export) but, if that's the case, you can trim the leading and trailing zero characters.

Aquilis · Post by **Aquilis** » Sun Mar 27, 2011 10:41 am

Yes, In actual tables it's loading correctly.
But when any records get's rejected, All the column data will get inserted into a single column of reject table as tab separated value through Column Export stage.

In the same tab separated value column , leading & trailing values getting inserted for decimal data.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 27, 2011 1:25 pm

Correct. Read my earlier response again.