Page 1 of 1
Left Zero fill, force to field length
Posted: Wed Jan 20, 2010 3:09 am
by ulab
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
Posted: Wed Jan 20, 2010 3:37 am
by priyadarshikunal
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?
Posted: Wed Jan 20, 2010 4:32 am
by ArndW
The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string.
Posted: Wed Jan 20, 2010 5:15 am
by ulab
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,
Posted: Wed Jan 20, 2010 5:20 am
by priyadarshikunal
what is the datatype you are using for that field?
Left Zero fill, force to field length
Posted: Wed Jan 20, 2010 5:21 am
by ulab
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,
Re: Left Zero fill, force to field length
Posted: Wed Jan 20, 2010 5:22 am
by ulab
ulab wrote:ArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...
Char data type
Posted: Wed Jan 20, 2010 5:27 am
by ray.wurlod
I'd bet Char(8) - am I right? So you can only get eight characters in that field.
Posted: Wed Jan 20, 2010 5:28 am
by priyadarshikunal
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.
Posted: Wed Jan 20, 2010 5:29 am
by ulab
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)
Posted: Wed Jan 20, 2010 8:29 am
by chulett
Then perhaps Right('000000000000':Trim(In.AD-ACCT-ID),12) would work.
Posted: Thu Jan 21, 2010 4:34 am
by ulab
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
