Summurize in DB tool field types are different from the inputs to the tool

[zhenming91]

Hello Alteryx Community, 

I am perplexed with the following problem:

Say,

1. I have three columns that is read using Connect-in-DB and they all have datatype 'V_String'

2. I used a Formula-in-DB tool to convert the field 'Value' into datatype 'Float' using this SQL expression (CONVERT(Real,"Value")) and this conversion is confirmed successful with the browse tool

3. I tie the output of this formula-in-DB to a summurize-in-db tool, but noticed that the datatype of the previously converted field 'Value' has returned to V_String. The issue is that I would like to average the 'Value' but is not able to do so unless it is a numerical type. 

Help is really appreciated! 

Ming

[zhenming91 · Accepted solution]

Solved it by trial and error.

1. Create a new field in the Formula-in-DB tool and only by doing this you can define the data type. In my case, I selected Double

It seems the SQL expression does not guarantee that the data type conversion would be implemented. The data type must also be explicitly defined in the formula-in-DB tool.