0 (zero) padding for number datatype field

[sanjay]

Hi All,

Our Environment:
HP Unix 11i (11.23)
Oracle 10g R1 (10.1.0.4)
DataStage 7.5.1A EE

Usually, we read sequential file (ascii, field delimited) as input, do some transform and prepare sequential file (ascii, field delimited) as output, which inturn send to sql loader as input to load into target database.

Noticed that the output file, has padded (leading or trailing or both) with 0 (zero) for the number datatype fields.

The following record is one such example from the output (loader) file :

|2005-06-01 13:03:12|13:03:12|13| |||61433290454|61433499995|
|00000000.00|
|00000000.00|H3GA|B|On-net|MMS|0|||7055|B07F70C2-1DD1-71B2-AC07-8313276B
|0000|UNKNOWN|1|UNKNOWN|UNKNOWN|UNKNOWN|2005-09-07 18:10:55|
|0000000000000000000001135135.0000000000||MMH3|mmh|604800|0|UNKNOWN|UNKN
|OWN|082704134251c208c648|1|UNKNOWN|1|010805153223f82cda1c|3_1754428_|0|
|http://10.176.59.240:10021/mmsc?AE-0w0RfpLt_aM0Dv|
|0000000000000000000000288237.0000000000|
|0000000000000000000000444033.0000000000|
|0000000000000000000000887008.0000000000|
|0000000000000000000000420053.0000000000|
|0000000000000000000000575789.0000000000|30011221|518425|0|61433499995|6
|1433290454|H3GMMSC_MMSC_IN|1|en|AE-0w0RfpLt_aM0Dv|1|1|1|1|1|1|0|?|csd|0
||unconfirmed|0|en|0|0|0|normal|personal|?|1|0|37|0|
|0000000000000000000020050618.0000000000|0|1011|
|0000000000000000000000000000.0000000000|
|0000000000000000000000005465.0000000000|
|0000000000000000000000005465.0000000000

Value in output file / Actual Value
======================================
0000000000000000000001135135.0000000000 / 1135135
00000000.00 / 0
0000000000000000000000288237.0000000000 / 288237

Due to this, the loader file size is increased considerably.

Please share with us your thoughts, if you have experienced such scenario, to fix this to produce output file without leading or trailing zeros.

Fixing this, potentially could improve our DataStage job performance as the number of writes to the disk reduces considerably.

Thanks for your reading, awaiting for your response.
Srini

[pnchowdary]

Hi Srini,

First, you need to convert the numeric values into string type. Then use any combination of the below tranformations.

You can remove the leading zeroes by using the below tranformation for each field.

Code: Select all

Trim(InLink.Incol1,"0","L")

You can remove the trailing zeroes by using the below tranformation for each field.

Code: Select all

Trim(InLink.Incol1,"0","T")

alternatively

You can remove the leading and trailing zeroes by using the below tranformation for each field.

Code: Select all

TrimB(Inlink.Incol1)

That being said, you need to be carefull for the decimal fields, where you might want to retain two digits after the decimal.

In the above case, you need to manually append the required number of zeroes after the decimal point and then convert it back to the appropriate numeric type before writing it to the output.

There are a lot of different ways to tackle your problem, but I just presented a few easy things that came to my mind. Hope this helps you.

[ray.wurlod]

Does adding zero to each number remove the leading zeroes?