SQL query error #1064

Question

So I have this query:

SELECT 
   t1.password 
FROM Session AS t1 
INNER JOIN Locations AS t2 ON 
    t2.id = t1.location_id AND 
    SQRT( POWER( CONVERT(t2.longitude,float(10)) - 132.456, 2) + 
        POWER( CONVERT(t2.latitude,float(10)) - 132.456, 2) ) < 100 
WHERE t1.end_distribution = 0 AND t1.end_session = 0

and I get this:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float(10)) - 132.456, 2) + POWER( CONVERT(t2.latitude,float(10)) - 132.456, 2) )' at line 13

In MySQL, the syntax for CONVERT is: CONVERT(expression, type); you've got the parameters flip-flopped (reference). — Cᴏʀʏ
– Cᴏʀʏ, Commented Jan 23, 2013 at 22:18
I would try wrapping the "Convert(exp, type) - #" in parentheses. I don't know enough about mySQL, but based upon the Near it looks like it is expecting a "," after your float and it's finding a "-". Wrapping it in parentheses might appease the order of operation gods. — Eric J. Price
– Eric J. Price, Commented Jan 23, 2013 at 22:25
@Love2Learn: The parentheses shouldn't matter. Perhaps float(10) isn't a valid conversion type? — Cᴏʀʏ
– Cᴏʀʏ, Commented Jan 23, 2013 at 22:27

Hamlet Hakobyan · Accepted Answer · 2013-01-23 22:47:30Z

3

Use

CONVERT(t2.longitude, float(10))

MySQL CONVERT SYNTAX

UPDATE

Depending on documentation above you can't convert expression to type float, use decimal instead.

CONVERT(t2.longitude, decimal(10))

edited Jan 23, 2013 at 22:47

answered Jan 23, 2013 at 22:19

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hamlet Hakobyan Over a year ago

@user783476 Are you flipped in both places?

ionescho Over a year ago

thank you very much for the update.. i will test this and get right back at you

spencer7593 · Accepted Answer · 2013-01-23 23:37:37Z

0

You've got an invalid specification for the type on the CONVERT function. FLOAT(10) is not valid. It looks like your best alternative will be to use DECIMAL.

From the MySQL Reference Manual: http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html

<snip>

The type for the result can be one of the following values:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

</snip>

answered Jan 23, 2013 at 23:37

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Collectives™ on Stack Overflow

SQL query error #1064

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related