Removing Leading Space and Trailing Dot from Numeric Fields
Moderators: chulett, rschirm, roy
Removing Leading Space and Trailing Dot from Numeric Fields
Hello All,
I need to remove a leading space and trailing '.' from my source data.
The source is a mainframe file. The source field attributes are --
Native Type : DISPLAY NUMERIC
Length : 3
Scale : 0
Level Number : 3
USAGE : DISPLAY
SQL Type : Decimal
Storage Length : 3
Picture : PIC 93
Now lets say my Input is coming as ' 999.' -->> My desired output is '999'.
The Target Datatype is - Numeric(3).
Please advice on how to move ahead with replacing/removing it.. Tried using the Convert & Trim Functions.. But nothing positive..!!
I need to remove a leading space and trailing '.' from my source data.
The source is a mainframe file. The source field attributes are --
Native Type : DISPLAY NUMERIC
Length : 3
Scale : 0
Level Number : 3
USAGE : DISPLAY
SQL Type : Decimal
Storage Length : 3
Picture : PIC 93
Now lets say my Input is coming as ' 999.' -->> My desired output is '999'.
The Target Datatype is - Numeric(3).
Please advice on how to move ahead with replacing/removing it.. Tried using the Convert & Trim Functions.. But nothing positive..!!
Regards,
Jayanto
Jayanto
-
- Participant
- Posts: 34
- Joined: Tue Feb 26, 2013 9:36 am
- Location: Chicago
@Sudha01_vpr -- I tried Ereplace(), but it was not working.. It works only with strings..!!
Also, as per my findings in DSXchange-EReplace() works with version 9.1, and pxEReplace() is not advised to be used.. Am working with DataStage 8.7..
Let me try it once with DecimalToString(), but again the datatypes needed to be tweaked..!!
Also, as per my findings in DSXchange-EReplace() works with version 9.1, and pxEReplace() is not advised to be used.. Am working with DataStage 8.7..
Let me try it once with DecimalToString(), but again the datatypes needed to be tweaked..!!
Regards,
Jayanto
Jayanto
-
- Participant
- Posts: 34
- Joined: Tue Feb 26, 2013 9:36 am
- Location: Chicago
-
- Participant
- Posts: 34
- Joined: Tue Feb 26, 2013 9:36 am
- Location: Chicago
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Assuming that your data is comprised of whole numbers, you can change it to an Integer datatype before the output file (expanding on Priyad's suggestion). Alternatively, you have the option of specifying a format string (C_format or Out_format options) in the column's metadata in the output sequential file stage.
There was a similar thread to this from just a week or two ago...a little bit of searching might have turned it up.
Regards,
There was a similar thread to this from just a week or two ago...a little bit of searching might have turned it up.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
This should also work:-
Code: Select all
Left(DecimalToString(Inputcol),Index(DecimalToString(InputCol),".",1)-1)
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or, perhaps simpler:
Code: Select all
Field(DecimalToString(Inputcol),".",1)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
In COBOL, display numerics can be treated as a subset of alphanumeric. The key is to make sure you provide "space" for the decimal.
The sign is usually incorporated in the first (rarely) or last (standard) digit. The EBCDIC character displays instead of the numeric. Ex. unsigned 0 = 0 (x'F0'), positive 0 = } (x'C0'), negative 0 = { (x'D0'). C and D are also the final half-byte in signed packed decimals.
Code: Select all
PIC 9(3) == Char(3)
PIC 9(3)V9(2) == Char(6)
etc.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872