String to Integer with JDBC /ODBC sql .

Question

I am facing severe problem with JDBC ,converting string to integer.

String query = "Select F2, F3, F4 from myTable ORDER BY {fn  CONVERT(INT,F4)}" ;

error: Syntax error in ORDER By clause;

Answer 1

The columns mentioned in the order by clause need to be exactly the same as they appear in the select clause. You should use the CONVERT function in the select clause as well. Also, you can reference selected columns by number to avoid repeating all that stuff:

SELECT f2, f3, CONVERT(INT, f4) FROM myTable ORDER BY CONVERT(INT, f4)

or

SELECT f2, f3, CONVERT(INT, f4) FROM myTable ORDER BY 3

Answer 2

Not sure about JDBC, but the escape sequence in ODBC must be: {fn CONVERT(F4, SQL_INTEGER)}