0

I am trying to execute the following query:

select MAX(CAST(T.progress AS DECIMAL)) as maxProgress from default_APN_TRACKING T where T.smartlet = 'Life-Pension Selector' GROUP BY T.trackingId

Which throws me the following Exception:

java.lang.Exception: Exception on sql query:select MAX(CAST(T.progress AS DECIMAL)) as maxProgress from default_APN_TRACKING T where T.smartlet = 'Life-Pension Selector' GROUP BY T.trackingId

Basically, i got a table with tracking IDs, and a string representing the progresses ("100.0", "66.6", "33.3", ...). For each trackingId, i want to get the highest progress.

I Agree having Integers would make more sens, but it's a constraint I have to deal with. So i tried using the CAST. Also Attempted CAST(T.progress AS DECIMAL(10,5)) without success.

The same query without the CAST works just fine, but sorts alphanumerically ("66.6" > "100.0"). How should I tackle this problem?

Thanks for the help!

EDIT: Copying the same query directly inside SQL WorkBench works perfectly. Seems like Java.sql does not like my query for some reason.

NGauthier
  • 885
  • 8
  • 17
  • Do `CAST (T.progress as FLOAT)` - would it work? – Stoleg Dec 11 '13 at 23:06
  • Any chance T.Progress could be NULL or some non-numeric value? See this question to check for numeric values: http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql – Sparky Dec 11 '13 at 23:12
  • @Sparky: The test data is "0.0", "33.3", "66.6" and "100.0", which should all be valid strings to parse. – NGauthier Dec 11 '13 at 23:38
  • @Stoleg: I don't think there is cast to float in mysql, will check that and edit this comment. – NGauthier Dec 11 '13 at 23:40
  • @NGauthier, MySQL has datatypes `FLOAT` and `DOUBLE`. http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html – Stoleg Dec 11 '13 at 23:42
  • 1
    CAST(column AS DECIMAL) is valid (but apparently meaningless -- it seems to be equivalent to CAST ... AS SIGNED) ... CAST(column AS DECIMAL(m,n)) is also a valid expression. There *should* be more content in that error message, to tell *something* about the actual nature of the exception. – Michael - sqlbot Dec 12 '13 at 00:06
  • Unpacked the exception, got nothing out of "getCause()". Would really have loved some explicit message. This java sometimes... – NGauthier Dec 12 '13 at 13:03
  • @Stoleg: FLOAT and DOUBLE yielded the same error. As if nesting a cast in a max was not supported. But it should be, according to documentation. – NGauthier Dec 12 '13 at 13:17
  • If you are having problem with `select cast()` then it is problem with data. What datatype that column is? If it is a character (which would explain alphabetical sort) then there is data that cannot be converted to numbers. Eg. try `select progress from default_APN_TRACKING where progress REGEXP '^-?[0-9]+$';` – Stoleg Dec 12 '13 at 15:05
  • Calling the exact same query, copy pasted from the exception itself, and running it under MySQL Workbench on the exact same data, works just fine. Java.sql seems to be where the problem is happening. – NGauthier Dec 12 '13 at 15:08

0 Answers0