A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, April 15, 2009

MySQL, how I hate thee edition 51984897

Coworker asked me to take a look at a query that was behaving oddly.
Ordering by a activationdate and then value, yes, the actual column
name is `value`. No, the ordering was working correctly but the data
type for Value is varchar(20) and it holds decimal data. Yes, that's
one of many sins in the flat file data holding system because it
certainly isn't an RDBMS. "Oh, you'll just need to conver that to a
floating point type. Should be 'cast(`Value` as decimal(10,2) as
Value'"

Heh-heh, if only that were the case. Have I ever mentioned that we're
on an ancient-ass version of MySQL---4 and some change. A few minutes
of the infuriating "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..." Check the manuals, yes, that should be it. Go
go google power and hey, look at that. Converting a string to number
(decimal or int) isn't supported.

Well, it's not supported by the CAST operation. But if you round it,
then MySQL will be happy to convert a string to number-based datatype.
Thus ''round(`Value`, 2) as Value'

/facepalm

No comments: