3

Hi guys I have this data extension (name is just example):

extension1

Name | Data Type

Field1 | Text

All data inside of Field1 is number and there is no alfabet. My goal is to change this field data type into number. Since I can't change the data type directly on the data extension. I plan to capture all data in this field by using sql and put it into another field in another data extension with number data type.

so I write an sql query

SELECT CONVERT(INT,Field1) AS Field2 FROM extension1

and then I put the result in another data extension:

extension2

Name | Data Type

Field2 | Number

I got no error but i got no result either. please if anybody has any idea about this, please help. If my explanation is confusing please ask. I'll be happy to explain more. thanks

UPDATE: I forget to tell that values in Field1 contain comma(,) and there are variations to how many numbers are behind the comma

DATA EXAMPLE:

219,975

69,25

364,2

80

713,5333

that is all the variations

  • 6
    This topic is related to Salesforce, since ExactTarget is a Salesforce company. Please do not close. – Kelly J Andrews Jan 16 '14 at 13:00
  • I'm testing this without commas, and not getting anything back using CAST or CONVERT – Kelly J Andrews Jan 16 '14 at 15:20
  • Are the commas then decimal places? – Kelly J Andrews Jan 16 '14 at 15:40
  • yes, numbers after comma is the decimal. If thats what you ask and we don't really care about the decimal as long as the data type can be changed into number – Faris Jan 16 '14 at 15:41
5

After some testing - you are able to directly change from string to number without using CAST or CONVERT. The data from the string field, must match the target data extension field, however.

If the field data type is NUMBER, your STRING must be:

  • An integer
  • You cannot import decimal values into a column with this data type. If you need to import decimal values, use the Decimal data type instead
  • This type accepts values from -2,147,483,648 to 2,147,483,648

This worked with the strings "123","456","789". I was able to directly move them from a string field to a number field using:

SELECT Field1 FROM data_extension

If the field data type is DECIMAL, your STRING must be:

  • A number with a decimal point
  • When you define a field as decimal, you indicate the total maximum length of the field and how many places after the decimal the field can hold. For Length, the first field is the total maximum number of digits and the second field is the maximum number of digits after the decimal point.

I was able to make this work by using the following:

SELECT REPLACE(Field1,',','.') as 'Field1' FROM data_extension

If you truly don't care about the decimals, and want those to be integers, use something like this:

SELECT FLOOR(REPLACE(Field1,',','.')) as 'Field1' FROM data_extension
  • I got same result, no error but no result. this happen because of the fault in designing phase. We capture the field value from our ERP system using API calls running realtime. So it is not feasible to do export import process even though we can automate it. – Faris Jan 16 '14 at 14:12
  • Let me see if we can do any type changing at all. Our queries are great, but I've never tried this. – Kelly J Andrews Jan 16 '14 at 14:13
  • I know. Its pretty fast and impressive. I got this kind of empty result also from other case, and the problem was in data length and also nullable/not so the query must be working but there might be something wrong with the target data extension field setting – Faris Jan 16 '14 at 14:16
  • How to change? I tried using decimal(18,4) in the SQL query and set the result field with the same data type decimal(18,4) – Faris Jan 16 '14 at 15:32
  • 1
    It works! I see now. So the problem is that exacttarget consider dot(.) instead of comma(,) to define decimal and there is no problem for data extension to capture values with different data type as long as all the values are match to the accepting field in data type. Thanks a lot Kelly. I owe you twice – Faris Jan 17 '14 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.