Sorting behavior

bbrown

When sorting text columns the native database will sort letters then numbers. The SQL version reverses this sort or places numbers before letters.

When using the SQL version I've been able to get the native behavior by using Navision's temporary tables. The are created on the client and behave the same in either database.

My current problem is I have a native database and need to sort a text column like SQL does. Any thought?

mstallmann

I believe the difference is with CODE fields, not text fields. If so, then for the code field, use a text field. i.e. Populate the text field with the same data as the code field. This is how SQL sorts, as it is a character field in SQL... Obviously, if this is quite a bit of data, this is a bad idea...

bbrown

mstallmann wrote:

I believe the difference is with CODE fields, not text fields. If so, then for the code field, use a text field. i.e. Populate the text field with the same data as the code field. This is how SQL sorts, as it is a character field in SQL... Obviously, if this is quite a bit of data, this is a bad idea...

I have used the approach you mention to get around the Code vs. Varchar behavior.

This issue is with Text fields. Create a table where the primary key is a text field. Try it in both native and SQL.

Navision (Native) gives them:

WHLASH
WH0003
WH9205



Navision SQL gives: (What they want)

WH0003
WH9205
WHLASH

mstallmann

I am probably missing something, as your example shows a pattern, but other values might produce other results, but what about sorting descending (DESC)?

bbrown

mstallmann wrote:

I am probably missing something, as your example shows a pattern, but other values might produce other results, but what about sorting descending (DESC)?

That would result in: (not what is needed)

Navision (Native) gives them:

WH9205
WH0003
WHLASH


Navision SQL gives: (What they want)

WHLASH
WH9205
WH0003


Thanks for the responses. The search continues....

bbrown

Used ADO to hold records in memory then sorted and processed. Solution came from one of our customers.