[QODBC ALL] Get Max RefNumber - Varchar to Integer Conversion

Problem Description

The RefNumber field in QODBC tables (e.g., SalesReceiptLine, SalesOrderLine, InvoiceLine) is a varchar type. Standard SQL aggregation functions like MAX(RefNumber) return the highest lexicographic (alphabetical) value, not the highest numeric value. For example, '9999' sorts higher than '40107' as a string.

Symptoms

• SELECT MAX(RefNumber) FROM SalesReceiptLine returns '9999' when the actual next sequential number should be 40107.
• Attempting to cast RefNumber directly with standard SQL CAST - e.g., SELECT MAX(CAST(RefNumber AS numeric)) FROM SalesReceiptLine - produces a QODBC syntax error: [SQL syntax error] Expected lexical element not found: = <identifier>
• The QODBC driver does not support standard SQL CAST for this purpose.

Cause

1. The RefNumber field is defined as a varchar, so MAX() performs string comparison rather than numeric comparison.
2. QODBC uses ODBC scalar functions (not standard SQL CAST) for type conversion in SELECT statements.

Environment

Resolution

Use the ODBC CONVERT scalar function to cast RefNumber to an integer before applying MAX():

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine;

This converts the varchar RefNumber to an integer type, allowing MAX() to return the correct highest numeric value.

SQL Examples

Get max RefNumber from SalesReceiptLine

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine;

Get max RefNumber from SalesOrderLine

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesOrderLine;

Use with WHERE clause for date filtering

SELECT MAX({fn CONVERT(RefNumber, SQL_Integer)}) as MaxRefNumber FROM SalesReceiptLine WHERE TxnDate >= {d '2024-01-01'};

Additional Notes

• The ODBC {fn CONVERT(field, SQL_Integer)} function works with any varchar field that stores numeric values and needs integer comparison.
• If some RefNumber values contain non-numeric data (e.g., free-text references), convert those to a standard format first - otherwise the conversion may silently exclude or misinterpret those records.
• This approach works in both QODBC Desktop and QODBC Online drivers.

Related Articles

• QODBC - SQL Language Support (Syntax)

Support Tickets

• MQR-566-94291

RefNumber MAX CONVERT SQL_Integer varchar SalesReceiptLine type conversion