83
Views
5
Comments
Solved
Error converting data type nvarchar to numeric
Question

I have an excel file with a column (Enrollment) that contains blank cells, number strings, and asterisks. I bootstrapped it into an entity and am using the entity in an aggregate with TextToDecimal to change strings into numbers, and unparsable strings to 0. 

In a mobile screen, TextToDecimal works as expected in an expression and blank cells/asterisks are replaced by 0s, and other strings are converted to numbers. However, TextToDecimal does not work when I try to create a new attribute in the aggregate. I get the following error. 

I want to create a new attribute so that I can sort my records by enrollment in the screen.

Am I missing something about the TextToDecimal function? I am using the following expression.

TextToDecimal(MAContractDirectory202006.Enrollment)

Thanks for your help

Rank: #136
Solution

Hi Rajan,


the functions that are available in Outsystems outside and inside an aggregate are not the same, and those that are available in both places (such as TextToInteger()) don't have the same implementation, so might work differently when you use them inside an aggregate then when used outside an aggregate.

In case of a TextToInteger(), it gets translated into a CONVERT function in sql, and this function can't deal with non numeric data.  When used outside an aggregate, it gracefully turns everything it can't handle into a 0.

So what are your options ?

1) unfortunately, I don't think there is a function available in outsystems doing a try_convert inside an aggregate anybody know of one ?

2) this is a bit too specific and messy to my liking, (i.e. will only work if the only char is indeed a *) but it might be good enough for your use case: in your calculated column, do something like this :

If((attribute ="*" or attribute = ""), 0, TextToDecimal(attribute))

3) use a sql node instead, with something like

SELECT try_convert(decimal(6,2), {entity}.[attribute])
FROM {entity}

4) convert before adding to your database

is there a function to the * and empty string.  Do you sometimes need to know them.  If not, you can change your attribute definition to decimal, and change your bootstrapping code to do the conversion between excel and your database value.


Hope this helps,

Dorine

Rank: #89

Hi Rajan,

You are make sure , you have numeric data in entity "Enrollment" column.

If one of row is content text in "Enrollment" column than not able to convert text to numeric.

Please check your data once.


Hope this will help you.

Regards

Rahul Sahu

Rank: #136
Solution

Hi Rajan,


the functions that are available in Outsystems outside and inside an aggregate are not the same, and those that are available in both places (such as TextToInteger()) don't have the same implementation, so might work differently when you use them inside an aggregate then when used outside an aggregate.

In case of a TextToInteger(), it gets translated into a CONVERT function in sql, and this function can't deal with non numeric data.  When used outside an aggregate, it gracefully turns everything it can't handle into a 0.

So what are your options ?

1) unfortunately, I don't think there is a function available in outsystems doing a try_convert inside an aggregate anybody know of one ?

2) this is a bit too specific and messy to my liking, (i.e. will only work if the only char is indeed a *) but it might be good enough for your use case: in your calculated column, do something like this :

If((attribute ="*" or attribute = ""), 0, TextToDecimal(attribute))

3) use a sql node instead, with something like

SELECT try_convert(decimal(6,2), {entity}.[attribute])
FROM {entity}

4) convert before adding to your database

is there a function to the * and empty string.  Do you sometimes need to know them.  If not, you can change your attribute definition to decimal, and change your bootstrapping code to do the conversion between excel and your database value.


Hope this helps,

Dorine

Rank: #398

Hi Rajan Kumar,

Better to use something like this. If the conversion is not possible then pass the default value.

if( TextToDecimalValidate(MAContractDirectory202006.Enrollment) = true, TextToDecimal(MAContractDirectory202006.Enrollment),0.0).

I used this for one of my logic. It seems to work for me. 



Rank: #50249

Thank you, Dorine. The #2 approach worked like a charm. I agree with your sentiment about it being too specific, but so far I haven't encountered any other character in that attribute, so I should be okay. I do not think OutSystems Aggregates support regular expressions; when I tried them, it didn't work.


I didn't try other approaches but investigate them in the future.

Thanks Rahul and Pranav for your ideas as well.