SQL and Sort

Hi, Probably this question has been asked many times, but here it is again: One of our clients is running Navision 4.00 in a SQL environment. We upgraded from 3.01 to 4.00. It seems to be that sorting orders are not quiet the same as in Native Navision. I.e 1…20 is in SQL 1,10,20,3…9. Is there any way to change that? Thanks! Roelof de Jong.

Hi, the easyest way to change this is to add 0 in front of short numbers. 1 = 01, 2 = 02 …

As Valentin wrote… and I on another site… :slight_smile: Kine

Or change all your Code fields to the Variant subtype to get the correct integer ordering.

It has to do with the Collation that you use. When you set up a Database, you can choose. For example if you use “SQL Sort Order” and select “Western-European dictionary sort order, code page 1252, case-sensitive, accent-sensitive (51)” your Code Fields will be sorted like 1, 2, 3, 10, 20, 100, 101 If you use “SQL Sort Order” and select “Binary sort order, code page 850 (40)” your Code Fields will be sorted like 1, 10, 100, 101, 2, 20, 3 I have not really tried, what happens when you use “Windows Collation”, but think it depends if you select binary or not. Cheers Daniel Zimmermann

Daniel I dont know how you arrived at that. Only the Code field Subtype property changes this ordering - the database collation has no influence because a collation does not understand the semantics of pure integer ordering versus character - that is alphanumeric - ordering, weather it is dictionary or binary. A SQL_VARIANT type has this ability (which is used when the Subtype is set to Variant) and can separate the pure integers from the alphanumeric.

Hi Robert, yes you are right, thought I tried it out, but now that I look at it again, it looks like I had a look at a Swiss Version (where the field was allready set to Variant) and a international Version, where this is not the case. Sorry