Left Zero fill, force to field length

ulab · Post by **ulab** » Wed Jan 20, 2010 3:09 am

Hi Friends,

Am new to Data stage, am looking for a logic which can fill '0'(zeros) at the left side of the input colomn value, just as below

FromTable, ToColumn, Type, Length, Formating
uniquename, AD-ACCT-ID, 9(12), Numeric, 12, Left Zero fill, force to field length

I have done a search but could not able to find any post, please give me a helping hand

priyadarshikunal · Post by **priyadarshikunal** » Wed Jan 20, 2010 3:37 am

define the field as Number and specify the length, it will automatically pad 0's on left to make it of same length.

are you writing to database or a flat file?

ArndW · Post by **ArndW** » Wed Jan 20, 2010 4:32 am

The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string.

ulab · Post by **ulab** » Wed Jan 20, 2010 5:15 am

ArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...

from the above statement, this one is not giving the results as expected, but in fact the out put is as below in bold

c1 AD-ACCT-ID c2 c3
@10296505000000 000 000

the bold 8 characters are the output of the field AD-ACCT-ID

yes am writing the output data into a Seq File,

priyadarshikunal · Post by **priyadarshikunal** » Wed Jan 20, 2010 5:20 am

what is the datatype you are using for that field?

ulab · Post by **ulab** » Wed Jan 20, 2010 5:21 am

ArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...

from the above statement, this one is not giving the results as expected, but in fact the out put is as below in bold

c1 AD-ACCT-ID c2 c3
@10296505000000 000 000

the bold 8 characters are the output of the field AD-ACCT-ID

yes am writing the output data into a Seq File,

ulab · Post by **ulab** » Wed Jan 20, 2010 5:22 am

ulab wrote:
ArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...

Char data type

ray.wurlod · Post by **ray.wurlod** » Wed Jan 20, 2010 5:27 am

I'd bet Char(8) - am I right? So you can only get eight characters in that field.

priyadarshikunal · Post by **priyadarshikunal** » Wed Jan 20, 2010 5:28 am

When datatype is char, datastage pads the character defined in environment variable. hence the length will be 12 always and the syntax posted by Arnd wont work.

you might like to trim the leading spaces to get the desired result.

ulab · Post by **ulab** » Wed Jan 20, 2010 5:29 am

ray.wurlod wrote:I'd bet Char(8) - am I right? So you can only get eight characters in that field. ...

char(12) on either ends(in source and target as well)

chulett · Post by **chulett** » Wed Jan 20, 2010 8:29 am

Then perhaps Right('000000000000':Trim(In.AD-ACCT-ID),12) would work.

ulab · Post by **ulab** » Thu Jan 21, 2010 4:34 am

chulett wrote:Then perhaps Right('000000000000':Trim(In.AD-ACCT-ID),12) would work. ...

thanks to all for their time, the problem got resolved with the following logic in the source stage(Oracle DB)

LPAD(supl.uniquename,12,'0') as uniquename

Once again thanks to All

DSXchange