Order by problem

Ignition
1

When I make a table and under the SQL Query and put in ORDER BY then whatever ASC and hit ok I always get this error.

Exception: Error running query:
SQLQuery(query=SELECT ID, Name, Address, Phone FROM Customers2
WHERE Name LIKE ‘%%’
ORDER BY Name ASC, database=WEDatabase)@0ms
On: Search Test.Root Container.Table.data
caused by GatewayException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
caused by SQLServerException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Ignition v7.3.1 (b496)
Java: Sun Microsystems Inc. 1.6.0_29

Not quite sure what to do to fix it.

2

Sorting on “LIKE”'d fields with wildcards can be finicky with data types depending on the flavor of SQL.

Try this query:

SELECT ID, Name, Address, Phone FROM Customers2 WHERE Name LIKE '%' ORDER BY cast(Name as varchar(500)) ASC

The logic is that % can match null, but you can’t sort null so the interpreter throws an error.

EDIT: You are successfully running Java 6 with the 29 update? How is that working for you?

1 Like
3

Which database are you using (MySQL, MS SQL Server)?

4

It is a MS SQL Server. Your code worked gbuehler thanks!