german_nav_developer: “Wutschen und Wedeln”: Sortieren und Filtern in Dynamics NAV - Unterschiede zwischen SQL Server und Native - Dynamics CRM: Blogs

Blog bot · 20.05.2011, 21:06

Was das “Wutschen und Wedeln” für den gemeinen Zauberlehrling, ist das “Sortieren und Filtern” für den Dynamics NAV Anwender. Im Hinblick auf den Wegfall des Native Server zum nächsten Major Relase, möchte ich diesen Artikel nutzen, um auf bekannte, aber auch vielerorts unbekannte Unterschiede zwischen dem SQL Server und dem betagten Native-Server einzugehen. Damit löse ich auch endlich ein Versprechen aus dem letzten Jahr ein und entlaste mein Gewissen ein wenig. Herr E., dieser Artikel ist “Ihrer”.

Dieser Beitrag enthält sehr viele Screenshots. Das ist gewollt, da ich mir vorgenommen habe, dass dieser Beitrag länger werden soll, als die Buildnummern-Übersichten von Sebastian.

Mal sehen…


<h1></h1>
<h1>Vorbereitung</h1>

Zunächst meine Einstellungen für die Tests, diese entsprechen den Standardeinstellungen für die Sortierung in einer Deustchen Datenbank (SQL Server):

Ich habe eine Tabelle mit einigen sinnvoll ausgewählten Daten für die Folgenden Beschreibungen befüllt. Tatsächlich handelt es sich um ein Subset der bekannten CRONUS-Artikelnummern. Es wird hier primär der Datentyp Code behandelt, allerdings ebenso Text, wenn es um die Filterung geht.


<h1>Sortieren</h1>

Wie Sie erkennen, handelt es sich auf der linken Seite um die Standardsortierung von Codefeldern auf dem SQL Server, während in der Mitte die Standardsortierung in einer Native-Umgebung zu sehen ist. Rechts findet sich die Sortierung von Dateien im Windows Explorer, identisch zur SQL-Sortierung.

Ziemlich offensichtlich erkennen Sie den Unterschied in der Sortierung. Der SQL Server sortiert genau so, wie man es in einer Datenbank für Textfelder erwartet, während in der Native-Umgebung eine Art Mischsortierung zu sehen ist.

Es gibt aber Möglichkeiten, das Verhalten auf dem SQL Server durch Nutzung der Feldeigenschaft “SQL Data Type” anzupassen. Diese steht explizit für Felder vom Typ Code zur verfügung.

Es sind folgende Werte erlaubt:
<ul>[*]Varchar: Die Feldwerte werden wie Text behandelt und als Text sortiert, auch reine Zahlenwerte [*]Integer: Alle Werte werden als Integer behandelt und alphanumerische Werte sind nicht erlaubt. Weiterhin wird der Wert 0 genutzt um einen leeren Text darzustellen und führende Nullen sind nicht möglich [*]Variant: Die Feldwerte werden auf Basis ihres Basisdatentyps sortiert. Numerische Werte werden dabei nach den alphanumerischen dargestellt. Führende Nullen für ausschließlich Numerische Werte sind nicht möglich [*]BigInteger: Verhält sich wie der Datentyp Integer, allerdings sind größere Zahlen möglich (von -9.223.372.036.854.775.807 bis 9.223.372.036.854.775.807) [/list]



Wechsel des SQL Data Type sind möglich, natürlich nur bei entsprechend kompatiblen Daten. Sind beispielsweise alphanumerische Daten enthalten, dann wird der Wechsel zu Integer natürlich mit einer Fehlermeldung quittiert. Die Beispiele der verschiedenen Sortierungen als Bild, für Integer habe ich selbstverständlich die alphanumerischen Werte entfernt.

Natürlich ist das alles nichts neues, manchmal ist es aber sinnvoll, sich diese Informationen nochmals ins Gedächtnis zu rufen. Interessanter wird es auf jeden Fall bei der Filterung, speziell im letzten Teil.


<h1>Filtern</h1>

Zum sanften Einstieg habe ich eine kurze Tabelle vorbereitet, mit der zunächst einige geraffte Hinweise auf alle zur Verfügung stehenden Operatoren und Ausdrücke und deren Wirkung bei der Filterung von Datensätzen gebe:
<table width="851" cellpadding="2" cellspacing="0" border="1">
<tbody>
<tr>
<td width="60" valign="top">

Symbol / Operator
</td>
<td width="216" valign="top">
<blockquote>

Bedeutung
</blockquote>
</td>
<td width="138" valign="top">
<blockquote>

Beispiel 
</blockquote>
</td>
<td width="435" valign="top">
<blockquote>
Beschreibung
</blockquote>
</td>
</tr>
<tr>
<td width="60" valign="top">
=
</td>
<td width="216" valign="top">Ist gleich</td>
<td width="138" valign="top">
=1900-S
</td>
<td width="435" valign="top">Datensatz mit dem Wert “1900-S”.</td>
</tr>
<tr>
<td width="60" valign="top">
?
</td>
<td width="216" valign="top">Platzhalter für ein Zeichen</td>
<td width="138" valign="top">
11?0
</td>
<td width="435" valign="top">Datensätze mit Werten, die mit “11” beginnen, auf “0” enden und an dritter Stelle ein beliebiges Zeichen aufweisen.</td>
</tr>
<tr>
<td width="60" valign="top">
*
</td>
<td width="216" valign="top">Platzhalter für kein oder beliebig viele Zeichen</td>
<td width="138" valign="top">
*S
</td>
<td width="435" valign="top">Den Datensatz “S” oder Datensätze die auf “S” enden und beliebig viele Zeichen enthalten.</td>
</tr>
<tr>
<td width="60" valign="top">
@
</td>
<td width="216" valign="top">Groß- und Kleinschreibung ignorieren</td>
<td width="138" valign="top">
@*s
</td>
<td width="435" valign="top">Den Datensatz “S”, “s” oder Datensätze die auf “S” bzw. “s” enden und beliebig viele Zeichen enthalten.</td>
</tr>
<tr>
<td width="60" valign="top">
<
</td>
<td width="216" valign="top">Kleiner als</td>
<td width="138" valign="top">
<1900
</td>
<td width="435" valign="top">Datensätze die alphanumerisch kleiner als “1900” sind.</td>
</tr>
<tr>
<td width="60" valign="top">
<=
</td>
<td width="216" valign="top">Kleiner / Gleich</td>
<td width="138" valign="top">
<=1900
</td>
<td width="435" valign="top">Datensätze die alphanumerisch kleiner oder gleich “1900” sind. </td>
</tr>
<tr>
<td width="60" valign="top">
>
</td>
<td width="216" valign="top">Größer als</td>
<td width="138" valign="top">
>70200
</td>
<td width="435" valign="top">Datensätze die alphanumerisch größer als “70200” sind.</td>
</tr>
<tr>
<td width="60" valign="top">
>=
</td>
<td width="216" valign="top">Größer / Gleich</td>
<td width="138" valign="top">
>=70200
</td>
<td width="435" valign="top">Datensätze die alphanumerisch größer oder gleich “70200” sind. </td>
</tr>
<tr>
<td width="60" valign="top">
<>
</td>
<td width="216" valign="top">Ungleich</td>
<td width="138" valign="top">
<>1900-S
</td>
<td width="435" valign="top">Datensätze die nicht “1900-S” beinhalten, also ungleich sind.</td>
</tr>
<tr>
<td width="60" valign="top">
|
</td>
<td width="216" valign="top">Entweder / Oder</td>
<td width="138" valign="top">
1000|70201|80022
</td>
<td width="435" valign="top">Datensätze die entweder “1000” oder “70201” oder “80022” enthalten, also max. drei.</td>
</tr>
<tr>
<td width="60" valign="top">
&
</td>
<td width="216" valign="top">Und</td>
<td width="138" valign="top">
1*&*S*
</td>
<td width="435" valign="top">Alle Datensätze die mit “1” beginnen und ein “S” irgendwo ab der zweiten Stelle beinhalten. Z.B. “1S”, “1xxxS, “1xxSx”</td>
</tr>
<tr>
<td width="60" valign="top">
..
</td>
<td width="216" valign="top">Intervall / Range</td>
<td width="138" valign="top">
19..701
</td>
<td width="435" valign="top">Alle Datensätze größer oder gleich “19” sind (“19”, “190”, “1900”) UND kleiner oder gleich “701” (“70000”, “70001, “70010”)</td>
</tr>
</tbody>
</table>



Sofern nicht anders angegeben, verhält sich eine Native-Umgebung hier wie der SQL Server. Ich gebe zu jedem Beispiel den zugehörigen T-SQL Befehl an, um das Verhalten noch zu verdeutlichen. Ist rechts noch ein zweiter Screenshot zu sehen, dann besteht ein Unterschied zum Native-Verhalten. Dieser ist jeweils beschrieben. Legen wir los:

Es folgen einige einfache Filterungen, zu denen nicht viel gesagt werden muss. Vielleicht dies, dass der SQL Server zwei Platzhalter für eine LIKE-Verknüpfung kennt, nämlich Prozent “%”, was dem NAV-Stern entspricht und steht für keines oder beliebig viele Zeichen. Den Unterstrich “_” sieht man eher selten. Dieser entspricht dem NAV-Fragezeichen und dient als Platzhalter für ein alphanumerisches Zeichen. Der Stern “*” hat im T-SQL keine Platzhalter-Funktion und entspricht einem normalen Zeichen.

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" = ’1900-S’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’11_0’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’%S’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Text 1" LIKE ’%s’)) ORDER BY "Code 1"



Diese Filterung ist wieder etwas interessanter. Die NAV-seitige Übergabe bzw. die Ersetzung für das “@”-Zeichen sind alle bekannten Entsprechungen für den abgefilterten Wert. Der unten stehende T-SQL Ausdruck filtert auf alle Werte, die auf “s”, “S”, “š” oder “Š” enden. Die möglichen Zeichen werden hier auch über die eingestellte Codepage bestimmt. Die eckigen Klammern fassen also eine Gruppe von Zeichen zusammen, die nur einzeln vorkommen dürfen, also ein einzelnes Zeichen.

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Text 1" LIKE ’%[sSšŠ]’)) ORDER BY "Code 1"

 

Nun wieder etwas entspannte Kost:

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" < ’1900’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" <= ’1900’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" > ’70200’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" >= ’70200’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" <> ’1900-S’)) ORDER BY "Code 1"

 

Die erste Filterung unter Nutzung logischer Operatoren, hier das ODER-Zeichen (Pipe), welches in das Schlüsselwort “OR” umgesetzt wird:

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" = '1000' OR "Code 1" = '70201' OR "Code 1" = '80022')) ORDER BY "Code 1"

 

In Kombination mit Platzhaltern:

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’701%’ OR “Code 1” LIKE ‘702%’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’1%’ OR “Code 1” LIKE ‘´%S%’)) ORDER BY "Code 1"

 

Der Schwenk auf UND:

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’1%’ AND “Code 1” LIKE ‘´%S%’)) ORDER BY "Code 1"

 

Jetzt wird es spannend. Ich führe nochmals einen einfachen Platzhalter-Filter auf, um dann auf direkt zum Intervall- bzw. Range-Operator zu springen. Wichtig ist hier sich nochmals die bisher bekannte Umsetzung eines Sterns in NAV in ein Prozent-Zeichen im T-SQL und die Nutzung des LIKE-Operators bewusst zu machen.

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" LIKE ’701%’)) ORDER BY "Code 1"

 

Aber jetzt kommt die besondere Ausnahme: Ein Stern “*” in NAV führt im T-SQL nur fast immer zu einem LIKE mit dem Platzhalter Prozent “%”. Es gibt eine Ausnahme von der Regel. Und zwar bei Nutzung des Range- oder Intervall-Operators. Wird dieser genutzt, dann findet zur Erstellung des T-SQL Konstrukts keine Umsetzung des Sterns statt. Stattdessen wird der Stern 1:1 übernommen und die Auswertung erfolgt als normales Zeichen, welches sich, je nach Codepage, in die normale Sortierung einreiht. Der Grund dafür ist, dass ein Platzhalter nur für LIKE-Vergleiche erlaubt ist, hier aber (höherwertig) ein Bereich ermittelt werden soll.

Die Ermittlung der passenden Datensätze lautet also auf “alles was kleiner oder gleich ’701*’ ist”. Wie Sie sehen ist auf dem SQL Server mit dieser (und wahrscheinlich der meisten) Codepage-Einstellungen der Stern kleiner als die “0”, da auf der linken Seite (SQL Server) die Werte “70100” und “70101” fehlen. Der Stern liegt also in der Codepage höher als die Null.

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" <= ’701*’)) ORDER BY "Code 1"



Auch der folgende Filter zeigt Unterschiede zwischen SQL Server und der Native-Variante, da wieder nicht auf LIKE umgesetzt werden kann.

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" >= ‘180*’ AND "Code 1" <= ‘701*’)) ORDER BY "Code 1"

SELECT * FROM "Demo Database NAV (6-2)"."dbo"."CRONUS AG$My Sorting" WITH (READUNCOMMITTED) WHERE (("Code 1" >= ‘19’ AND "Code 1" <= ‘701’)) ORDER BY "Code 1"



Auch wenn einige der obigen Informationen durchaus bekannt sind, so denke ich, dass speziell die Umsetzung von Intervall- oder Range-Ausdrücken für viele neu ist, mir ging es jedenfalls damals im Dezember, und da kommt wieder ein Stück schlechtes Gewissen hoch, genau so. Möglicherweisie hilft dieser Artikel, Fehler bei der Migration von Native-Umgebungen, die Sie teilweise noch vor sich haben, zu vermeiden.

Carsten Scholling 

Microsoft Dynamics Germany Microsoft Customer Service und Support (CSS) EMEA 

Email: cschol@microsoft.com Microsoft Connect: http://connect.microsoft.com Online Support: http://www.microsoft.com/support Sicherheitsupdates: http://www.microsoft.de/sicherheit

Microsoft Deutschland GmbH Konrad-Zuse-Straße 1 D-85716 Unterschleißheim http://www.microsoft.de<div style="clear:both;"></div>

Далее