CODEWITHSUNDEEP
phpsqlpostgresql
zenlord
zenlord

Reputation: 340

SQL and negative floats

consider this rather simple SQL-query:

"SELECT * FROM transactions 
WHERE amount BETWEEN $cost_amount AND 0
AND acc_int IN ('Contant', '647-0191181-84')
AND dos_costs_id IS NULL
AND date + interval '30 days' >= to_date('$cost_date', 'YYYY-MM-DD')
ORDER BY (amount <> to_number('$cost_amount','99999999D99')), date DESC"

I have used this exact query for two years now to display all possible transactions on an account for our incoming transactions. All those 'amounts' were positive floats.

Now I'm reusing the query for outgoing transactions, so the amount is always negative. Whenever the amount is an integer, this works, but it halts as soon as the value is a float. So the combination of a '-' before the value and a 'comma with two trailing numbers' at the end of the amount returns a syntax error, f.e.:

Query failed: ERROR: syntax error at or near "," LINE 2: WHERE amount BETWEEN -327,13 AND 0

I have tried adding quotes around the amount, but that didn't help.

Upvotes: 1

Views: 6553

Answers (2)

sgeddes
sgeddes

Reputation: 62831

You need to use to_number to convert ###,## to ###.##. SQL won't accept the comma as a number. Try something like this which will work for commas and periods:

SELECT * 
FROM YourTable
WHERE amount BETWEEN to_number('-327,13', '99G999') AND 0

And here is the SQL Fiddle.

Good luck.

Upvotes: 1

abc667
abc667

Reputation: 514

Try to use dot instead of comma :)

Upvotes: 5

Related Questions