Left Zero fill, force to field length
Moderators: chulett, rschirm, roy
Left Zero fill, force to field length
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
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
Ulab----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
from the above statement, this one is not giving the results as expected, but in fact the out put is as below in boldArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...
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----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Left Zero fill, force to field length
from the above statement, this one is not giving the results as expected, but in fact the out put is as below in boldArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...
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----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow
Re: Left Zero fill, force to field length
Char data typeulab wrote:ArndW wrote:The transform function Right('000000000000':In.AD-ACCT-ID,12) on a string. ...
Ulab----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
you might like to trim the leading spaces to get the desired result.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
thanks to all for their time, the problem got resolved with the following logic in the source stage(Oracle DB)chulett wrote:Then perhaps Right('000000000000':Trim(In.AD-ACCT-ID),12) would work. ...
LPAD(supl.uniquename,12,'0') as uniquename
Once again thanks to All
![Smile :)](./images/smilies/icon_smile.gif)
Ulab----------------------------------------------------
help, it helps you today or Tomorrow
help, it helps you today or Tomorrow