Decimal to Varchar Conversion

[Former Member]

I have source Oracle  column COMPONENT decimal (28,7) . Am trying to load this column into Destination sql server , COMPONENT varchar(384)

Source data is  like 7153104.0000000 this, i want to load this data into destination with out the decimal values like this 7153104.

I tried using cast , to_char but still am getting the data into destination with decimal values only.How can i avoid them ?

cast(BOM.COMPONENT,'varchar(384)')

when i try this, am getting a warning as well

[Query:Query]
The data type conversion will be used to convert from type <varchar(64)> of mapping expression <to_char(BOM.COMPONENT,'varchar(384)')> to type <decimal(28,7)> of target column <COMPONENT>. (BODI-1110410)

[former_member186897]

Hi samuel v,

If you are sure that stored data in this column is always a number and it would never be a decimal number (however your source column has defined this field as decimal) then you can directly code like below:

cast(cast(BOM.COMPONENT,'int'),'varchar(384)')

First the above code will convert into int and then into the datatype of your destination column. This will remove the precisions (.0000000) from the number. You should not get any warning now.

If the column contains something like 7153104.1570000 then you need to use decimal in place of int while casting in the above code else you would be loosing some values.

Hope this helps. Do let us know if you it works.

Regards,

MSA

[Former Member]

Hello

If your target column is COMPONENT varchar(384), then you should investigate why you are getting the message ... to type <decimal(28,7)> of target column <COMPONENT>.

If you have multiple transforms, check you have the correct datatype specified for this column in all transforms.

Michael

[former_member205887]

Below should work for you

to_char(7153104.0000000, '9999999999999999');

[Former Member]

i tried this, its still pulling the decimal digits

to_char(BOM.COMPONENT,'9999999999999999') this is what i used.

still the output is 7153104.0000000

[former_member205887]

It should work, i am using Source as Oracle and Target as SQL Server and it works perfectly fine for the same data you gave

[Former Member]

i tried this bala, but still i dont know why am getting deciamls in my destiantion.

i dont have any other transformation.its just direct load from source to destination.

[former_member205887]

1) Did you try it in script ?

2) Try to place another query transform after converting to Varchar and before loading to target, debug and check the value of it