NAV Team: Temporary Tables: Different Type of Sorting Order in SQL Server Environment

[Blog bot]

Probably most of the partners have already noticed this and changed the C/AL code accordingly in order to let their customized application works properly.

In ALL versions of NAV up to now (NAV 2009 R2) the SORTING of Temporary Tables / Temporary record variables has always been determined using a C/AL sorting "collation" type. Even in SQL Server environment, where it is possible to select the proper collation (File > Database > Alter > Collation) for the database, Temporary Tables / Temporary record variables use always C/AL sorting order.This is very important to know since it may lead to a wrong business processing of data when using e.g. record looping (REPEAT .. UNTIL cycles).

This behavior has been maintained like this for 2 main reasons:
1) Temporary Tables / Temporary record variables get created independent on the server, so they cannot read the collation and sorting from SQL Server and use this.
2) If MS NAV Development team change the way that Temporary Tables / Temporary record variables are sorted, then this might break compatibility with a lot of existing solutions that would suddenly sort differently.

Below you will find a simple demonstration of this assumption. For completeness, I have made the example both for Classic Client (Forms) and RoleTailored Client (Pages) but the behavior is the same.

1. Install a CRONUS database (W1 or whatever localized version)

2. Add these records in Table 6 "Customer Price Group"

CodeDescription1Code 12Code 22.12Code 2.12200Code 2003Code 3300Code 300C3Code C33. Add and enable one key to table 18 Customer with "Customer Price Group" field (if this is not already active). Save and compile (CTRL+S) table 18 Customer.

4. Add those codes to Customers.

No.NameCustomer Price Group01121212Spotsmeyer's Furnishings1 01445544Progressive Home Furnishings201454545

New Concepts Furniture2.1201905893Candoxy Canada Inc.20001905899Elkhorn Airport301905902London Candoxy Storage Campus30010000Cannon Group SpAC35. Create a New Codeunit, e.g. Codeunit 50000 "Test TempTable" with these global variables and code snippet:

Global variables

NameDataTypeSubtypeLengthCustTempRecordCustomerCustRecordCustomerSet the Temporary property of the CustTemp variable to Yes.

C/AL code snippet:

// Copyright © Microsoft Corporation. All Rights Reserved.

// This code released under the terms of the

// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

CustTemp.DELETEALL;







Cust.RESET; // Populate CustTemp Table

Cust.SETCURRENTKEY("Customer Price Group");

Cust.SETFILTER("Customer Price Group",'<>%1','');

IF Cust.FINDSET THEN REPEAT

CustTemp.INIT;

CustTemp.TRANSFERFIELDS(Cust);

CustTemp.Name := 'TEMP ' + COPYSTR(CustTemp.Name,1,25);

CustTemp.INSERT;

UNTIL Cust.NEXT = 0;



Cust.RESET; // Run Page/Form - Normal table

Cust.SETCURRENTKEY("Customer Price Group");

Cust.SETFILTER("Customer Price Group",'<>%1','');

Cust.FIND('-');

IF ISSERVICETIER THEN

PAGE.RUN(PAGE::"Customer List",Cust)

ELSE

FORM.RUN(FORM::"Customer List",Cust);



CustTemp.RESET; // Run Page/Form - Temporary table

CustTemp.SETCURRENTKEY("Customer Price Group");

CustTemp.FIND('-');



IF ISSERVICETIER THEN

PAGE.RUN(PAGE::"Customer List",CustTemp)

ELSE

FORM.RUN(FORM::"Customer List",CustTemp);



6. Save and compile (CTRL+S) the codeunit.



7. Run the Codeunit. (You can also add this Codeunit as an action in RTC. The results within Forms and Pages is the same).



8. Now compare the 2 Forms opened (they are one up in front the other) and their different sort order (show column "Customer Price Group" to clearly see the difference in sorting order):



Temporary (C/AL type dependent)Normal (SQL Server collation dependent)112232.1220020030032.12300C3C3Since from the next version there will not be any support for Native database (where this C/AL sorting coming from) there have been speculations about changing this behavior in order to have the SORTING for Temporary Tables / Temporary record variables equal to the SQL Sorting (in short, for SQL Server based environments to have the same sorting order for normal tables and temporary tables).

If you think this is a remarkable feature that could / should to be changed in a future version, please log your request into MSCONNECT:

https://connect.microsoft.com/dynamics

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Best Regards,

Duilio Tacconi (dtacconi)

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA





Источник: http://feedproxy.google.com/~r/Micro...vironment.aspx