ultranerds — 2014-05-23T06:29:00-04:00 — #1
I've got a really weird issue with a SQL SELECT query. See below:
SELECT usd FROM glinks_Costs WHERE original_currency = "gbp" and amount = 0.1
It gives me no results. Even though I can see the data in the table (side-by-side in the pic I posted above).
I'm not a mySQL novice - but this one has got me stumped. If I do the same query, but with a large number, i.e:
SELECT usd FROM glinks_Costs WHERE original_currency = "gbp" and amount = 5.25
The issue seems to occur as soon as I go below 1. The field is a FLOAT, so I'm not sure whats going on
patche — 2014-05-23T06:46:10-04:00 — #2
Hmm; in your screenshot query it appears you are putting quotes around the 0.1 (though your code example isn't). Does that make a difference? I can't see too much wrong with your query from the current data you provided us.
r937 — 2014-05-23T07:18:22-04:00 — #3
there's your problem right there
floats are approximate
change the column to DECIMAL using whatever scale and precision are appropriate for your monetary values
ultranerds — 2014-05-23T11:11:42-04:00 — #4
@Patche - sorry, that was just an example. I was trying with and without (I'm aware you are not meant to quite numbers, but was just giving it a go as it wasn't working
@r937 - eugh, how could I be so stupid!!! Thats got it working perfectly now. Thanks