decimal output

[suresh_dsx]

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

[kcbland]

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.

[gmt_etldev]

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

[suresh_dsx]

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