Sort order on a Code type field

KeithMMoore · 2008-11-04

In a native 4.0 DB the Vendor table "No." field appear to sort in what I consider a fairly natural way:

1
2
10
20
21
30
100
1A
1B

In SQL however that same list goes as

1
1A
1B
10
100
2
20
21
30

Is this simply a reflection of how the native DB handles the code field vs SQL or is there a setting/property I am missing somewhere? Our client would very much prefer the natural order they have enjoyed thus far.

Savatage · 2008-11-04

One way to always avoid this is to have codes like that all the same length
so 18 would be
00001
00018
00101
01560

Also I read something about changing a value to Variant see posts
viewtopic.php?f=33&t=21759
viewtopic.php?f=23&t=4055

garak · 2008-11-04

These field is a alphanumeric field with alpha numeric sort order. So, on SQL, not native (Native do it wrong/his own way) the "numeric" sort is not "correct" but the alpha sort is.
If you need the order 1,2,3,4,5,...,10,11,12....,111,112,113, etc you can set leading Zeros infron of.

For more infos search also the forum. There are many topics about these theme.

Regards

KeithMMoore · 2008-11-04

Thanks for input - I was afraid that might be the answer

I'll have to break the news to the client this afternoon.

ara3n · 2008-11-05

You can change the property "SQL Data Type" to Variant.

tro#1 · 2009-05-14

All,

I have exactly the same problem.
The number series setup was not done correctly. And now we have following document numbers in the system:

VF09-9998
VF09-9999
VF09-10001
VF09-10002
etc.

But Sequal sorts it like
VF08-0999
VF08-1000
VF08-10000
VF08-10001
...
VF08-1001
VF08-1002
...

That is why I changed the "SQL Data Type" of the Number to Variant.
Nothing has changed...
Is there anything else I need to check?

Thank you in advance.

ara3n · 2009-05-14

That property changes the sort if you are only using integers in your code field.

tro#1 · 2009-05-14

Thank you for your answer.

So I assume I cannot change the sorting that it would also work for alpha-numeric codes...

ara3n · 2009-05-14

yes that's correct.

Sort order on a Code type field

Answers

Categories