Source column is col1 data type is Decimal(13,2).
In the tranformer i am passing to a column derivation.
If (Trim(Link.EMP_COl1) = '' Or IsNull(Link.EMP_COl1)) Then '0.00' Else If Lnk.REL = 1 Then Link.EMP_COl1 Else '0.00'
Target column length: Decimal(9,2)
Example Source :487.67
Output shuold get: 487.67
But i am getting the 000000467.67
If it is null i need to get the value 0.00 but i am getting the out put 000000000.00
please share with me the solution.
Thanks and Regards,
Suresh
decimal output
Moderators: chulett, rschirm, roy
Decimal datatype has leading zeroes, which area meaningless if you're loading directly into a database. If you want character based data if writing to a sequential file (which is just about the only place you care if your decimal data contains leading or trailing zeroes, switch the datatype and suppress the zeroes.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 7
- Joined: Mon Dec 17, 2007 1:37 am
- Location: Boston, MA
Hello,
I am having the same problem. I have tried to "switch the datatype and suppress the zeroes", but it is still not working.
In the transformer:
Input: Decimal 38,2
Stage Variable: Decimal 38,2
Output: Varchar 40 with a
DecimalToString(COLUMN_NAME_HERE,"suppress_zero") conversion.
I still get 0 where I need 0.00
Any ideas?
Thanks,
Barbara
I am having the same problem. I have tried to "switch the datatype and suppress the zeroes", but it is still not working.
In the transformer:
Input: Decimal 38,2
Stage Variable: Decimal 38,2
Output: Varchar 40 with a
DecimalToString(COLUMN_NAME_HERE,"suppress_zero") conversion.
I still get 0 where I need 0.00
Any ideas?
Thanks,
Barbara
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
Hi,
Now I am able to get the value of the column is null then 0.00
Source column Col1 Datatype/length is Decimal/13.2
First created a stage variable Stg1
Derivation expression: If (Trim(Link1.Col) = '' Or IsNull(Link1.Col)) Then '0.00' Else Link1.Col
data type/length of the stage variable: Deciaml 13.2
in the target column name is FINAL_COL
Data type/Length varchar/16
Note: I gave varchar 16 because 13+2+1(Dot) so total 16 is length.
Derivation: If Right(DecimalToString(Stg1),2) = '00' Then TrimF(DecimalToString( Stg1,'suppress_zero')) : ".00" Else If Right(DecimalToString(Stg1),1) = '0' Then TrimF(DecimalToString(Stg1,'suppress_zero')) : "0" Else TrimF(DecimalToString(Stg1,'suppress_zero'))
Finally if we need to do null handling on decimal (ex: 13.2) better we can convert into string.
It will be easy for us to work but careful with spaces.
Thanks for help the issue is resolved
Suri_dsx
Now I am able to get the value of the column is null then 0.00
Source column Col1 Datatype/length is Decimal/13.2
First created a stage variable Stg1
Derivation expression: If (Trim(Link1.Col) = '' Or IsNull(Link1.Col)) Then '0.00' Else Link1.Col
data type/length of the stage variable: Deciaml 13.2
in the target column name is FINAL_COL
Data type/Length varchar/16
Note: I gave varchar 16 because 13+2+1(Dot) so total 16 is length.
Derivation: If Right(DecimalToString(Stg1),2) = '00' Then TrimF(DecimalToString( Stg1,'suppress_zero')) : ".00" Else If Right(DecimalToString(Stg1),1) = '0' Then TrimF(DecimalToString(Stg1,'suppress_zero')) : "0" Else TrimF(DecimalToString(Stg1,'suppress_zero'))
Finally if we need to do null handling on decimal (ex: 13.2) better we can convert into string.
It will be easy for us to work but careful with spaces.
Thanks for help the issue is resolved
Suri_dsx