When we do the query:  "SELECT * FROM Invoice WHERE EditSequence > 1159825332"

1159825332 (EditSequence is 10 digit here), and this Invoice table also has some

EditSequence value like 957052586 (9 digit, some data migrated from the old QuickBooks).

Because EditSequence in Invoice table is varchar or char,

if we do the varchar comparison, 957052586 is greater than 1159825332,

but this is not what we want.

Than we try the query: "SELECT * FROM Invoice WHERE int (EditSequence) > 1159825332"

unfortunately, the driver doesn't seem support it.

Try using:

SELECT * FROM Invoice WHERE {fn CONVERT (EditSequence,SQL_Numeric)} > 1159825332

See: What Numeric Functions can be used in QODBC?  for more info on numeric functions.

by using function conver to SQL_Numeric, (actually we want to conver it as INTEGER) the following select count is working:

$sql = "SELECT COUNT(EditSequence) FROM   Invoice WHERE ({ fn CONVERT(EditSequence,SQL_INTEGER) } > 0)

thanks a lot.

but we have another problem here:

after select count, we need to select records order by the EditSequence which should be based on converted numeric (or integer) EditSequence,

we don't want to order by varchar or char, the following query is not working:

SELECT * FROM   Invoice ORDER BY { fn CONVERT(EditSequence,SQL_INTEGER) }

we got error: Expected lexical element not found <identifier> (SQL-42000)

we also tried to put parenthesis: ORDER BY ({ fn CONVERT(EditSequence,SQL_INTEGER) }), not working.

the only working situation is: ORDER BY EditSequence (without any converting)

The curious case we tried is:

we select converted EditSequence order by EditSequence, the following query is working:

SELECT ({ fn CONVERT(EditSequence,SQL_Numeric) }) as EditSequence, OtherFieldNames FROM   Invoice ORDER BY EditSequence

it prints out numeric EditSequence.

we don't know this select is order by the converted EditSequence or not.

but another problem is we can't run the following query:

SELECT ({ fn CONVERT(EditSequence,SQL_Numeric) }) as EditSequence, *  FROM   Invoice ORDER BY EditSequence

(I used * instead of other paticular field names), we do want select every field. we don't want to specify the other particular fields names.

please advise,

thanks a lot.