Handling Zeros with Decimal dataType
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Fri Sep 26, 2008 9:33 pm
- Location: Bangalore
Handling Zeros with Decimal dataType
Could you please anybody explain how to remove the Zeros with Decimal datatype:
I am having source Col with Datatype decimal[20,2] i am getting data for example 000000000000000038.50 but i want to make target like 38.50 means i want to remove only Leading Zeros not Trailing for this i tried to Covert Decimal to string with supress Zeros and also Trim(col,"0",'L') but i am not able to convert.
Please clarify if anybody knows In Advance
I am having source Col with Datatype decimal[20,2] i am getting data for example 000000000000000038.50 but i want to make target like 38.50 means i want to remove only Leading Zeros not Trailing for this i tried to Covert Decimal to string with supress Zeros and also Trim(col,"0",'L') but i am not able to convert.
Please clarify if anybody knows In Advance
Manu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can't remove zeroes if the data type is Decimal. DataStage puts them in to guarantee that the precision and scale are correct.
You don't need to remove zeroes if the data type is Decimal. It's the same value with and without them.
You can remove zeroes if you're converting to a string data type of some kind, particularly VarChar.
You don't need to remove zeroes if the data type is Decimal. It's the same value with and without them.
You can remove zeroes if you're converting to a string data type of some kind, particularly VarChar.
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.
Re: Handling Zeros with Decimal dataType
Trim(col,"0",'L') will work with string data types(varchar..) and not with decimal
manu.dwhds wrote:Could you please anybody explain how to remove the Zeros with Decimal datatype:
I am having source Col with Datatype decimal[20,2] i am getting data for example 000000000000000038.50 but i want to make target like 38.50 means i want to remove only Leading Zeros not Trailing for this i tried to Covert Decimal to string with supress Zeros and also Trim(col,"0",'L') but i am not able to convert.
Please clarify if anybody knows In Advance
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 30
- Joined: Fri Sep 26, 2008 9:33 pm
- Location: Bangalore
Thanks For your Quick Reply But i tried all the ways For Example:
Decimal to String(Col1,'Supress-Zero') This will remove all the Zeros from input But My requirement Like
000000023.20 here required output 23.20(Trailing Zero Required) If i use above function it will remove all the Zeros
and alo I tried after Converting To String Still Trim Not Working Like Trim(Col1,"0","L")
My Actual Input:Decimal [20,2] Ex:00000000020.00 out put any of the datatype But we require Output Like 20.00.
Please reply if any body having Ideas.
Regards,
Manu
Decimal to String(Col1,'Supress-Zero') This will remove all the Zeros from input But My requirement Like
000000023.20 here required output 23.20(Trailing Zero Required) If i use above function it will remove all the Zeros
and alo I tried after Converting To String Still Trim Not Working Like Trim(Col1,"0","L")
My Actual Input:Decimal [20,2] Ex:00000000020.00 out put any of the datatype But we require Output Like 20.00.
Please reply if any body having Ideas.
Regards,
Manu
Manu
-
- Participant
- Posts: 11
- Joined: Sat Mar 13, 2010 12:00 am
-
- Participant
- Posts: 11
- Joined: Sat Mar 13, 2010 12:00 am
-
- Participant
- Posts: 11
- Joined: Sat Mar 13, 2010 12:00 am
Trim(Col1,"0","L") will give 20.00 for 00000000020.00, but for 0000000000.15 it will give .15. i hope that is not required if the target field is of currency format...
plz correct me if i am wrong.
try with :
(if col1 <1 then '0' else ''):Trim(DecimaltoString(col1),"0","L")
this should work
Thanks - Sajal
plz correct me if i am wrong.
try with :
(if col1 <1 then '0' else ''):Trim(DecimaltoString(col1),"0","L")
this should work
Thanks - Sajal
-
- Participant
- Posts: 30
- Joined: Fri Sep 26, 2008 9:33 pm
- Location: Bangalore