Having issues with decimals In-Database

[anxs]

Im new to Alteryx but have experience in SAS and SQL. I am trying to run a query using the Connect In-DB function. This works correctly, the query is something like this:

Select
State,
sum(Sales) AS NewSales
From
Table1
Group By State

The query works fine. If I connect a Browse Data In-DB I see the results and these match with what I expect to get. For NewSales, some are decimals. When I connect a Data stream out to export the results I keep losing the decimals. Doesnt matter if i export to a csv or create an Alteryx database. I've tried to add a select step and change to decimal, ive tried to put the SQL statement in a regular (not in-DB) input tool, ive tried creating a new variable using the formula tool so this new one is a decimal. Nothing works. The Select In-DB tool doesnt allow me to change types and when i click on the Output properties of the Connect In-DB it says that the Type is Int64.

how can I get this to work?

[JordanB · Accepted solution]

Depending upon what database you are connecting to, have you tried to use a CAST function which would look like CAST (Field_Name) as DECIMAL (20,5) to change the field type. This may keep the data as fixed decimal through the output instead of int64.

Best,

Jordan Barker

Client Service Reprensentative 

[chris_love]

Can you try to Cast it in the SQL as a float?

[anxs]

this worked. thanks!