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

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

Rate This
  • Comments 0

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”. Smile

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. Winking smile Mal sehen…

 

Vorbereitung

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

image

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.

 

Sortieren

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.

Sortierung SQL Server

Sortierung NativeSortierung Explorer

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.

Feldeigenschaften Code

Es sind folgende Werte erlaubt:

  • 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)

 

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.

Sortierung SQL Server VarcharSortierung SQL Server Integer/BigIntegerSortierung SQL Server Variant

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.

 

Filtern

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:

Symbol / Operator

Bedeutung

Beispiel

Beschreibung

=

Ist gleich

=1900-S

Datensatz mit dem Wert “1900-S”.

?

Platzhalter für ein Zeichen

11?0

Datensätze mit Werten, die mit “11” beginnen, auf “0” enden und an dritter Stelle ein beliebiges Zeichen aufweisen.

*

Platzhalter für kein oder beliebig viele Zeichen

*S

Den Datensatz “S” oder Datensätze die auf “S” enden und beliebig viele Zeichen enthalten.

@

Groß- und Kleinschreibung ignorieren

@*s

Den Datensatz “S”, “s” oder Datensätze die auf “S” bzw. “s” enden und beliebig viele Zeichen enthalten.

<

Kleiner als

<1900

Datensätze die alphanumerisch kleiner als “1900” sind.

<=

Kleiner / Gleich

<=1900

Datensätze die alphanumerisch kleiner oder gleich “1900” sind. 

>

Größer als

>70200

Datensätze die alphanumerisch größer als “70200” sind.

>=

Größer / Gleich

>=70200

Datensätze die alphanumerisch größer oder gleich “70200” sind. 

<>

Ungleich

<>1900-S

Datensätze die nicht “1900-S” beinhalten, also ungleich sind.

|

Entweder / Oder

1000|70201|80022

Datensätze die entweder “1000” oder “70201” oder “80022” enthalten, also max. drei.

&

Und

1*&*S*

Alle Datensätze die mit “1” beginnen und ein “S” irgendwo ab der zweiten Stelle beinhalten. Z.B. “1S”, “1xxxS, “1xxSx”

..

Intervall / Range

19..701

Alle Datensätze größer oder gleich “19” sind (“19”, “190”, “1900”) UND kleiner oder gleich “701” (“70000”, “70001, “70010”)

 

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.

Filtern SQL 

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

 

Filtern SQL

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

  

Filtern SQL

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

  

Filtern SQL

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.

 Filtern SQL

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:

 Filtern SQL

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

  

Filtern SQL

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

  

Filtern SQL

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

  

Filtern SQL

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

  

Filtern SQL

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:

 Filtern SQL

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:

 Filtern SQL

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"

 

 Filtern SQL

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:

 Filtern SQL

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.

 Filtern SQL

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.

 

Filtern SQL

Filtern Native

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.

Filtern SQL

Filtern Native

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"

 

Filtern SQL

Filtern Native

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

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post