Code sorting in SQL

Case: We recently converted a fairly large (+20Gb db - heavily modified) N3-solution to 3.60 (SQL). The conversion went smoothly and the customer has been up and running for well over a month now, virtually, without any problems whatsoever. One problem - or should I say major irritation, though, remains - and this is a classic: the sorting of codefields in SQL as examplified below:SQL Native 1 1 10 2 100 3 2 10 3 100This, of course, happens because the Code datatype doesn’t exist in SQL and, default, is represented as Varchar. We faced 3 options: (1) Try to convince the Customer that this is an unavoidable “feature” in SQL, which, they simply have to live with. (2) Rename every Code field (or at least those used in keys) to establish correct sorting. (3) Change the property “SQL Data Type” of said code fields from to Variant (SQL_VARIANT in SQL 2000 only). No. (1), obviously, didn’t go down well with the customer and was quickly discarded. No. (2) was reluctantly abandoned given the time and work involved with respect to the db-size. So we were left with option (3): Initially - in a test environment - we changed the “No.” fields in tables 18 (Customer) and 23 (Vendor). Everything - as seen from Navision - apparently worked correctly, and so we deployed it to the production environment. We then experienced some problems with an external app. (using ODBC and C/FRONT) and applied the change to table 21 (Cust. Ledger Entry) and table 25 (Vendor Ledger Entry) as well and everything seemed to be ok. Soon after though, we experienced a disturbing thing: The primary key in the Customer-table was defect/corrupt - we could page up endlessly in the table!! This problem has been solved (exporting all data, reinstalling object, reimporting data) but needless to say we’re a bit worried now - have we chosen a wrong path? (1) Is this (Variant) a solution at all? Has anybody done it with success out there? (2) Do we need to change the property in every single code field in every single table (from a Navison pointview they’re still all codefields)? (3) If so; suggestions to do this in an efficient way are welcome - with respect to future upgrades as well. (4) Re. (3) I’m considering writing a report Using table 2000000041 field. Has anyone done this? Any thoughts, comments or recommendations are welcome…

I would think that 1) was the correct option. Of course there is option 4), i.e. use native db on 3.70. there is also option 1a), that’s where you leave everything as it is, then add an additional field (type integer), where you can sort by the old method. I would guess that with the path you have taken, that you must change every single field that is linked.

We can agree that Option 1) is “correct”, but the client - understandably - sees things differently (try filtering 1…3) Option 4) (native db) is not an option. The Client has a rigid SQL strategy. Option 1a) is totally out of the question for 2 reasons: 1) They dont’t always use integer values in their code fields (if they did “SQL Data Type” = Integer was a 5th option). 2) It’s simply not a solution, no offense, but think it through…[:0][V][xx(]

I think then that you must rename the records, iti s the only long term solution that will work.

This property can be changed programatically using the Field virtual table you mentioned, as well as using the Table designer. As you have guessed, since this is a Code field from C/SIDEs point of view, the underlying SQL type will not affect interraction between, for example, two Code fields in different tables where one if the default Varchar and one is a Variant. So it is not necessary to change all fields, you can be selective. Of course, the ordering will be different (no code, C/AL, CFRONT or whatever, should depend on the order of records). It is strange that re-building your data solved the scolling error, since this bug occurs based on the values you have which would be the same before and after. Therefore I would expect that you might see this happening again. Certainly there is no corruption in SQL terms regarding the primary key - don’t worry about that; it is a C/SIDE comparison error of Code field values that causes this endless scrolling - not the results returned from SQL Server. I would suggest reporting this error to MBS; hopefully they can reproduce it and get a fix shipped to you quickly (followed later by an official hotfix). I know that they have done this in numerous similar cases before.

Thx Robert, That was the answer I was hoping for. We’ll stick to what we’ve done so far (selective tables) and if the scrollingerror reappears report it to MBS.

quote:


When you set the value of the SQL Data Type property of a code field to Variant: The values in the field are compared and sorted according to their base data type. Numeric values are sorted after alphanumeric values. Data that is entered into the code field in Navision Attain is stored as either the VARCHAR or INTEGER base data type, depending on the value that has been entered. Any value beginning with “0” (zero) can be entered in the code field and is stored as an INTEGER base data type.


The above is taken from the onlinehelp. Our theory was that the property was put there specifically, to solve the codefield problem in SQL. If this is the case, it would seem logical to use Variant as default or even better provide functionality that enabled selecting the datatype globally (global option). In most cases, though, the problem only arises when the conversion from a native db is involved. Out of curiousity re. changing only selective tables: If for example you changed “No.” in table “G/L Account” to Integer but left every other reference to this field at its default value Varchar (“G/L Entry” etc.) won’t this have an affect on the SQL performance? I must admit that I find it hard to comprehend how SQL interprets relations between textfields and integerfields…

Just a precision on sorting There’s another difference between native code fields sorting and characters or variant SQL Fields which is more difficult to handle - In native DB : ‘AA’<‘A0’<‘A9’<‘0A’<‘9Z’ - In SQL : ‘0A’<‘9Z’< ‘A0’<‘A9’<‘AA’ I hope you could solve your problem Best regards

The SQL implementation knows nothing about C/SIDE table relations (except for modelling purposes), or any relationship made as part of C/AL logic. It just receives SQL select statements with parameters. Weather those inputs are Text, Integer values or whatever does not affect performance - because they are all ‘created equal’. The only issue would be weather SQL Server’s impementation of the Variant data type has a performance hit. I made some tests of this myself some time back and found no problem in indexes based on Variants. Yes, regarding the sorting, the SQL Variant sorts integers after alphanumerics and native places them before. Both decisions are somewhat arbitrary because there is no standard governing the order in which data types ‘groups’ (or families as SQL calls them) are placed.

quote:


Originally posted by robertc … So it is not necessary to change all fields, you can be selective. …


Whilst technically correct, I am not sure if I agree with this as a global statement.[;)] In my experience of moving a client from native to sql, most of the field issues arrise in the G/L. In the case of sorting customers and vendors, I don’t really even see this as an issue. The issue arrises where there is some level of Range filtering going on, as happens in the G/L a lot. I have seen numerous cases where after sql conversion, the Accounts Schedules are no longer correct. Of course in this case Robert I agree with you, since it is rare to create a sum of custmer detail entries based on a range of customer nos, but in general, sorting should be consistant for all field that point to a data source. On the other hand, I am sure youhave a lot more SQL experience than me, and I could be wrong.[?]

Yes, agreed David, if the application can cause filter problems because of comparisons between values of different fields that have a different order, then play it safe by changing all fields.

thanks.