Sorting Issues after conversion to SQL... version 4.0

ccb-esc

Let's see if I can explain this clearly...

There is a production order posting routine in codeunit 5407 (the exact code is not significant) that worked under C/Side but does not work after conversion to SQL. Investigation revealed that the problem is the inherent difference in sorting between the Navision 'code' datatype and and the SQL 'varchar' datatype.

The field in question is 'Operation No.' in the table 5409 Prod. Order Routing Line. In C/Side this field is type 'code'. The values that the client uses in that field are all numeric (90, 100, 560, etc.). The posting routine sets the key (which doesn't include the 'Operation No.' field) descending (ascending,false), and filters on a single production order. The records at that point are sorted numerically on Operation No. , since a C/Side 'code' field will sort numeric values numerically. The code needs to find the last Operation No., which it does, since the sort is descending, with a Find('-'). In C/Side this works fine because the dataset is sorted numerically descending by Operation No. (600,200,150,100,90,50,10).

In SQL, the 'Operation No.' field is type 'varchar,' which always sorts alphabetically. So when it gets to the Find('-') the records are not in order (90,600,50,200,150,100,10), it grabs the wrong record, and the procedure fails a few lines later.

The code in both versions is absolutey identical.

So the really puzzling thing is that the code is obviously expecting the Operation No. to be sorted numerically in the code field, so it is bound to fail, always, after conversion to SQL which forces the Operation No. to be sorted alphabetically.

Is this a widespread problem? We have never seen or heard of it before now, but changing all 'code' fields to 'varchars' seems like a very major conversion issue, with innumerable possible consequences.

Is there anything to be done??? Are there SQL settings we aren't aware of?

Thanks,

Chandler

DenSter

That's due to the fact that SQL Server does not have a data type that behaves the same way as the NAV Code type. We're stuck with what we have I'm afraid.

If you are sure that this customer only uses numeric values, you can set the "SQL Data Type" property to Integer, and it will sort accordingly. If your customer is using numeric as well as alpha, then you'll have to rename the records with equal length code field values, forcing the correct order that way, so you'd have 001, 010, 100, etc.

ccb-esc

Hadn't seen the SQL Data Type property! Thanks!

DenSter

It's one of those things Let us know if that fixes your problem

twdavis

does the SQL Data Type need to be set in every table that contains the initial table field? for instance, "Vendor No." now has the SQL Data Type set to integer. Customer Ledger Entry does not.
WIll these other tables need to be changed?

DenSter

Not unless you need the records to be sorted as a numerical value, and of course not if you need the code fields to contain alpha characters.

kriki

[Topic moved from Navision forum to SQL General forum]

Mauddib

I have been aware of the change in sorting between native and SQL for some time when you make the mistake of putting an integer into a code field and then try and sort on it. I ALWAYS avoid this.

However I am now on a customer site where they have gone to SQL 2005 and this issue is a big one for them because all their items have K* or J* as structure but they have other items from 1..1000 for representing "Delivery costs" and things like this.

This is also something I always avoid, I always try and make sure people do NOT EVER use the item table to represent things that are not items.

My question now is... since this company have already done this, and since a HUGE amount of their code is bases upon the filter:

SETFILTER("No.", '>1000');

what can i do now? How would you recommend rewriting the above filter so all items from 1..1000 get ignored by their code?

The database is HUGE so I want to avoid having to work out every table where the item no. has been stored and renaming it in every place.

DenSter

Have you tried SQL Type Variant? Do it on a test database first. You might also want to try another collation. Search the forum for that, there was a long and elaborate thread about that.

Waldo

You should always use Variant. Integer is just there for compatibility reasons. With Integer, using the AltSearchField goes crazy ... .

jwilder

Be careful with the SQL DataType you might run into some problems like we did. It actually changes the datatype in your SQL database so if you have any non NAV reports built on that table you will probably get Datatype conversion errors. A report that link the a varchar (Sales Invoice Header, No.) to a varchar (Sales Invoice Line, Document No.) in the past won't run since it now links an integer or variant in the Sales Invoice Header to a varchar in the Sales Invoice Line.

Also if you go to change back to the original datatype of varchar you may not get the original primary key back. For example we change the Customer Table No. field to be an integer. We realized it was a mistake and change it back. Customer's that had numbers like 000001 or 000002 originally were now permanently changed to 1 and 2. The really annoying thing is that the change is only in the customer table and not in any other table. So the customer table has a primary key of 1 but all other tables (Cust Ledger, Detailed Cust. Ledger etc...) still have 000001.

I'm sure there are fixes for these problems but just be aware and be careful. One final thought is that Temporary tables still sort the C/SIDE way. If you absolutely need to sort in code the "old way" you could use a temporary table if it is feasible.

DenSter

That is a very good point you are making, especially with more companies going to SRS for their reporting. Thanks for the warning.

jlandeen

It's been a while since I've run into problems like this but one thing to check is how the database is configured. The SQL Collation setting can affect how records are sorted in the database.

Normally I use SQL Collation that is accent and case insensitive (i.e. code page 54). I suggest this because it seems to offer the closest kind of sorting to how standard Navision works in the Nataive database server.

However when you create a Navision database by default it wants to use Windows Collation - which of course exhibits the exact problems that you have described here.

jlandeen

Right...few other last things you may want to consider. If you have to change the SQL collation it may be faster to back the database up, delete the db, build a new one with the correct collation and then restore from fbk.

When making any of these big changes to Navision you will probably want to set the recovery model to simple so that the transaction logs do not grow huge, and then put them back to full once you have a database that is good to go.