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

Question

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 Answer

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.