Hi,
I am having a table with the following fields
When I use the filters in the table to show the data for CPL < 50, we expect it to treat the value like numbers, and now show values like 100, 200 etc in the result set.
Is there anyway I can fix this?
These columns are coming straight from the database and are mapped directly
Hey @achou! I don't think this should be happening 
To double check, is your query returning the Budget as a number? If you open up the State tab in your left panel and expand out your query as much as possible to show the Budget column data, does it show numbers?
Hi,
You are right, it is returning the data as strings.
What do I need to do to return the data as numbers? I have tried
select customers.customer_id as ID, CAST(customer_adwords_history.budget as decimal(10,2)) as Budget from customers
Do I need to use transformers here? I am loading around 4,000 rows here and would like to avoid using transformers as it might have an impact on performance
Hmm! Definitely understand not wanting to add another layer to 4k rows 
Have you tried:
SELECT *, CAST(budget AS DECIMAL) from table
or
SELECT *, budget::INTEGER from table
or maybe even
SELECT *, budget::NUMBER as budget_as_a_number from table
Let me know if none of those work! 
Hi Victoria,
CAST (customer_adwords_history.budget as INTEGER) as Budget
is working and returning data as integers, but I would like to see this working for decimals as well.
SELECT *, budget::NUMBER as budget_as_a_number from table gives me an error. I am using a Postgres database to make these queries.
SELECT *, CAST(budget AS DECIMAL) from table returns me the data in string format. Attached is the screenshot.
Unfortunately i'm still stuck here, trying to retrieve data as numbers.
I'm not sure if I'm missing something in Postgres or a functionality in retool
Hey @achou! I haven't been able to find a viable solution aside from:
return formatDataAsArray(data).map(obj => Object.assign(obj, {budget: obj.budget.parseFloat()}))
If you try the transformer, do you notice a big difference in performance?
Hi Victoria,
Thanks. I think it is a problem with the column type in the database.
I am storing it as numeric, and changing it to double-precision did the trick.
Let me double check with my other use cases, before I mark it as closed
Oh, awesome! Definitely let me know
Hi
This is working. Thanks a lot.
I'm still not sure why it doesn't work with numeric column, but changing the column type works for me for now.
Hi Victoria. I saw your answer to achou but not sure I understand it. I similarly am struggling with a string=>money related error using retooldb. I'm using a CAST on my SQL which returns a good value. But when I try to use it in a form field It returns an error that the value is a string. Any advice would be appreciated.
queryLaborRate is my query that returns unit_cost as a money. extCostInput is a number field where I want the calculation to be used. Error message in the image below.
Hey @Al_Sambar!
If you hover over your queryLaborRate.data.unit_cost, what does it display? Is it a string of a number? If so, you can convert it to a string to be used in the calculation like:
{{inputQty.value * parseInt(queryLaborRate.data.unit_cost)}}
Does that work for you?
Nope. Attaching a screenshot. Is it possible that issue is because your line has parseInt and the number, being a currency is actually a decimal? And, since the data is stored in retool as a money field, anyways, am I doing something wrong with my query that it is converting it to a string?
Potentially! Would you mind sharing a screenshot of your left panel’s state tab showing what queryLaborRate.data.unit_cost is?
