DSXchange

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

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.

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

u can use trim if the destination is char/varchar.
plz correct me if i am wrong

No. Even U cannot apply trim in this instance if source is decimal and target is varchar.

If the target is varchar just use decimaltostring(col_name,'supress_zero') that will work...

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

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.

Thanks - Sajal

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.

Thanks - Sajal

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

HI
Thanks for reply But Here what you suggested it may not correct for what i am expecting for exaple if i am having Source
Col1
00000000101.00
00000000055.12
00000000505.10

OutPutRequired:
101.00
55.12
505.10
Please give me any Ideas for the OutPut.
Thanks In Advance