Im Folgenden finden Sie die Anleitung zum Plug-in SQL Ausdrücke sowie einen exemplarischen Anwendungsfall in einer ausführlichen Schritt-für-Schritt-Anleitung.
Inhalt des Kapitels
1.3 Einordnung in die Gesamtsoftware
1.3.2 Verknüpfungen zu anderen Plug-ins
3.1 Mehrere Datenquellen aus unterschiedlichen Datenbanken
3.2 Datenquellenspezifische Syntax
Das Plug-in SQL Ausdrücke ermöglicht es Ihnen, für Ihre Datenbankabfragen individuelle SQL-Abfragen zu erstellen.
Diese lassen sich in sämtlichen Plug-ins, in denen mit Daten gearbeitet wird, nutzen, um den jeweiligen Datenpool nach Belieben zu spezifizieren.
Über das Plug-in SQL Ausdrücke haben Sie die Möglichkeit, eigene SQL-Abfragen zu erstellen.
So können Sie Ihre Daten, die Sie einem Plug-in hinzufügen wollen, ganz individuell spezifizieren – einmal angelegt lassen sich SQL-Abfragen plug-in-übergreifend überall verwenden.
Um für jeden Anwendungsfall genau angepasste SQL-Abfragen zur Verfügung zu haben, können Sie beliebig viele erstellen und diese nach den eigenen Vorstellungen benennen, so behalten Sie stets den Überblick.
Darüber hinaus prüft SimAssist gleich beim Erstellen einer SQL-Abfrage, ob diese in korrekter SQL-Syntax erstellt wurde und ob Sie entsprechend Daten bei deren Anwendung zurückerhalten.
1.3. Einordnung in die Gesamtsoftware
Das Plug-in SQL Ausdrücke ist Bestandteil des Moduls 2check, das darüber hinaus noch die Plug-ins Variablen und Checking enthält.
Das Plug-in SQL Ausdrücke steht Ihnen zur Verfügung, wenn Sie das Modul 2check für SimAssist lizenziert haben.
1.3.2. Verknüpfungen zu anderen Plug-ins
Da das Plug-in SQL Ausdrücke für das Spezifizieren von Datenbankabfragen zuständig ist, steht es mit sämtlichen Plug-ins in Verbindung, die ebenfalls in den Prozess der Datenabfrage involviert sein können.
Dazu zählen alle Plug-ins, denen Daten hinzugefügt werden können.
Abbildung 1 - Aufbau von SQL Ausdrücke
Im oberen Bereich des Plug-ins ist das Menü, das die Interaktionen Neu, Bearbeiten, Kopieren, Löschen, Abfrage prüfen, Als Datei und In die Zwischenablage enthält, positioniert.
Direkt darunter angrenzend befindet sich die Anzeige der bereits erstellten SQL-Abfragen. Nachfolgend werden die Icons der Spalte Status erklärt:
Icon |
Beschreibung |
|
Grüner Haken - Die Abfrage wurde überprüft und die Syntax ist gültig |
|
Rotes X - Die Abfrage wurde überprüft und die Syntax ist ungültig |
|
Blaues Fragezeichen - Die Abfrage wurde noch nicht überprüft |
Das Menü des Plug-ins SQL Ausdrücke hält die benötigten Funktionen bereit, um SQL-Abfragen zu erstellen sowie zu verwalten, die plug-in-übergreifend zur Datenabfrage verwendet werden können.
Im Folgenden werden nun die Interaktionsmöglichkeiten des Menüs einzeln vorgestellt.
Button |
Beschreibung |
|
Öffnet den Dialog zum Erstellen einer neuen SQL-Abfrage. |
|
Öffnet den Dialog zum Bearbeiten der aktuell markierten SQL-Abfrage. |
|
Kopiert die aktuell markierte SQL-Abfrage und lässt Sie für die Kopie eine Bezeichnung vergeben. |
|
Löscht die aktuell markierte SQL-Abfrage (Achtung: Es erfolgt keine Nachfrage). |
|
Prüft die erstellte SQL-Abfrage auf korrekte Syntax. |
|
Exportiert die ausgewählte SQL-Abfrage in eine txt-Datei. |
|
Exportiert die ausgewählte SQL-Abfrage in die Zwischenablage. |
|
Bitte beachten Eine Verwendung von einzelnen Pipes "|" in den SQL-Abfragen ist aus technischen Gründen nicht möglich. Der "||" Operator steht jedoch uneingeschränkt zur Verfügung. |
|
Kommentare im Plug-in SQL Ausdrücke Kommentare können an jede beliebige Stelle im SQL Ausdruck mit "--" eingefügt werden |
Um SQL-Abfragen zu erstellen, betätigen Sie den Button Neu im Plug-in-Menü – es öffnet sich ein entsprechender Dialog (siehe Abbildung 2).
Abbildung 2 - Dialog zum Hinzufügen einer neuen SQL-Abfrage
Zunächst müssen Sie eine eindeutige Bezeichnung vergeben, die Sie durch eine Beschreibung ergänzen können um die Identifizierung von erstellten SQL-Abfragen zu erleichtern.
Das Feld Kategorie kann als zusätzliche Möglichkeit für eine Sortierung der erstellen SQL Abfragen verwendet werden.
Um nun die SQL-Abfrage an sich zu erstellen, ziehen Sie per Drag&Drop die gewünschte Datenquelle, auf welche die SQL-Abfrage angewendet werden soll, in den Zielbereich des Dialogs.
Sie können dabei einzelne Spalten oder ganze Tabellen hinzufügen. SimAssist erstellt nun eine Standard SQL-Abfrage nach dem Muster: SELECT * FROM IhreTabelle/IhreSpalte
Diese Abfrage können Sie nun nach den SQL-Regeln gestalten und so ihre ganz persönliche SQL-Abfrage generieren.
|
Hinweis Ab SimAssist Version 9.1 können die SQL-Abfragen auch per Drag&Drop erstellt werden. Ziehen Sie dazu einfach beliebige Spalten mit der Maus an die gewünschte Stelle in der Abfrage. Die gewünschte(n) Spalte(n) werden an der aktuellen Cursorposition eingefügt. Dies ist auch bei bereits bestehenden SQL Statements möglich, heißt das bestehende Statement wird nicht überschrieben. |
|
Hinweis Wenn Sie mehr zur SQL-Syntax lesen möchten, können Sie hierfür unter anderem die Webseite der w3schools zu Rate ziehen (http://www.w3schools.com/sql/). Hier finden Sie sehr ausführliche Tutorials zur Anwendung von SQL. |
Vor dem Speichern der SQL-Abfrage empfiehlt sich eine Prüfung auf korrekte Syntax. Betätigen Sie dazu den Button Abfrage prüfen im Menü.
SimAssist überprüft nun, ob eine korrekte SQL-Abfrage vorliegt und gibt Ihnen in diesem Fall eine entsprechende Meldung aus (siehe Abbildung 3.1 und 3.2).
|
Bitte beachten: Bitte beachten Sie, dass Spaltennamen, welche ein Leerzeichen oder andere Sonderzeichen enthalten, mit eckigen Klammern ( [...] ) oder mit Anführungszeichen ( "..." ) umschlossen werden müssen. |
Abbildung 3.1 - Korrekte SQL-Abfrage |
Abbildung 3.2 - Korrekte SQL-Abfrage |
3.1 Mehrere Datenquellen aus unterschiedlichen Datenbanken
Seit SimAssist Version 8 ist es möglich, mehrere Datenquellen für ein SQL-Statement zu verwenden. Diese können dabei aus unterschiedlichen Datenbanken, SQL-Statements oder Tabellen stammen.
Das Ergebnis einer Abfrage ist eine Ergebnistabelle. Beim Verarbeiten von mehreren Datenquellen in einer Abfrage werden somit Daten aus mehreren Tabellen in einer Ergebnistabelle zusammengefügt.
Die als Quelle geltenden Tabellen können Tabellen oder Ansichten aus verschiedenen Datenbanken sowie die Ergebnistabellen aus anderen SQL-Statements sein.
Die Ausführung einer Abfrage übernimmt der jeweilige Datenbanktreiber. Da dieser i.d.R. nicht auf mehreren Datenbanken zugreifen kann, muss das Zusammenfügen in der Anwendung erfolgen.
Hierfür ist Folgendes notwendig:
1.Jede verwendete Tabelle (unabhängig von der Datenbank) wird in der Abfrage definiert (Bezeichnung: eindeutiger Name, Quelle: Datenbank + Element oder SQL-Statement)
2.Kopieren aller Daten der definierten Tabellen in eine temporäre SQLite Datenbank (alle Rohdaten, weil Bedingungen erst im nächsten Schritt ausgeführt werden können)
3.Ausführen der Abfrage in der temporären SQLite Datenbank
Der Modus „Erweitert“ kann über eine Checkbox aktiviert werden (siehe Abbildung 4).
Abbildung 4 - Modus Erweitert aktiv
In diesem Modus wird das Feld „Datenquelle“ durch ein Tabellen-Steuerelement „Datenquellen“ ersetzt, in dem Quelltabellen hinzugefügt, gelöscht und bearbeitet werden können.
Ist dieser Modus deaktiviert, bleiben das Verhalten und die Ansicht wie bisher. Die Verwendung von SQL-Abfragen als Datenquelle ist wie die Verwendung mehrerer Datenquellen nur im „Erweitert“-Modus möglich.
In der Spalte „Tabellenname“ im Bereich „Datenquellen“ muss ein eindeutiger Name vergeben werden. Eine Datenquelle wird durch Ziehen aus dem SimAssist-Datenbereich in den Datenquellenbereich im unteren Teil verbunden.
Die folgenden Datenquellentypen können auf diese Weise verbunden werden:
•Datenbanktabellen und -ansichten
•SimAssist SQL-Ausdrücke
•Tabellen aus SimAssist Datenanbieterschnittstelle
•Spalten aus dem Inhaltsbereich
Im SQL-Ausdruck können die Tabellen über den nutzer-vergebenen Tabellennamen referenziert werden. Eine Anpassung des Tabellennamens bewirkt keine automatische Anpassung im SQL-Ausdruck.
Der Inhalt der Spalte „Typ“ ergibt sich aus der verbundenen Datenquelle.
Abbildung 5 - Erweiterte SQL Abfrage
|
Bitte beachten Mit der Verwendung von SQL-Ausdrücken und Datenanbietern als Quelle ist eine Definition von Kreisreferenzen möglich (Statement 1 ist abhängig von Statement 2 und Statement 2 ist abhängig von Statement 1). Solche SQL-Ausdrücke können nicht ausgeführt werden. Eine Kreisreferenz wird dem Nutzer durch eine Fehlermeldung mitgeteilt und muss durch den Nutzer aufgelöst werden. |
3.2 Datenquellenspezifische Syntax
Nachfolgende Tabelle zeigt eine Übersicht unterschiedlicher SQL-Ausdrücke in der jeweiligen datenquellenspezifischen Syntax:
SQL Ausdruck |
Oracle |
Access |
SQLite |
DATETIME DATE TIME |
DATE 'YYYY-MM-DD' TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE |
Date() "m/dd/yyyy" Time() "h:mm:ss" |
date(timestring,modifier,modifier,...) "YYYY-MM-DD" time(timestring,modifier modifier,...) "HH:MM:SS" date time(timestring,modifier,modifier,...) "YYYY-MM-DDTHH:MM:SS" julianday(timestring,modifier,modifier,...) strftime(format,timestring,modifier,modifier,...) |
IIF |
IIF(search_condition,true_part,false_part) |
IIf([Action]='E','I',IIf([Action]='A','O','R')) |
(CASE WHEN ACTION = 'E' THEN 'I' ELSE (CASE WHEN ACTION = 'A' THEN 'O' ELSE (CASE WHEN ACTION = 'R' THEN 'R' END) END) END) |
DECODE |
decode(expression,compare_value,return_value,[,compare, return_value]...[,default_return_value]) |
--> see SWITCH |
--> see SWITCH |
SUBSTRING |
SUBSTR(input,start-pos[,length]) SUBSTRB(input,start-pos[,length]) SUBSTRC(input,start-pos[,length]) SUBSTR2(input,start-pos[,length]) SUBSTR4(input,start-pos[,length]) |
SUBSTRING(expression,start,length) |
substr(string,start,length) |
MID |
--> see SUBSTRING |
Mid(text,start_position,[number_of_characters]) |
--> see SUBSTRING |
TRIM |
TRIM(string_to_be_trimmed) |
LTRIM(string_to_be_trimmed) RTRIM(string_to_be_trimmed) |
trim(string,[trim_string]) |
SWITCH |
CASE {simple_case_expression|searched_case_expression} [else_clause] END |
Switch(expression1,value1,expression2,value2,...expression_n,value_n) |
CASE case_expression WHEN when_expression_1 THEN result_1 WHEN when_expression_2 THEN result_2 ... [ ELSE result_else ] END |
SQLite hat ein abweichendes Handling von Datentypen im Vergleich zu anderen Datenbanken. Dazu gehört auch, dass der Rückgabewert jeder Aggregatsfunktion immer den Datentyp Object hat.
Davon sind alle Aggregatsfunktionen betroffen.
Beispiele:
•Grundrechenoperationen wie Addieren usw.
•Statistische Operationen wie Min/Max/Avg/Sum
•Umwandlungsoperationen wie Cast("wert" as TYP)
Wenn die Daten in einer Spalte das notwendige Format haben, kann die SQL Abfrage so erweitert werden, dass die Spalte mit dem "richtigen" Datentyp zurückgegeben wird.
Das folgende Beispiel zeigt wie man das Maximum einer Double Spalte wieder als Double erhält.
|
Hinweis strftime('%u', date) returns the day of the week starting from monday = 1 strftime('%w', date) returns the day of week 0-6 with Sunday = 0 |
Die Typumwandlung in SQLite von "Object" zum gewünschten Datentyp erfolgt über folgenden Code vor der eigentlichen Abfrage:
TYPES (gewünschter Datentyp mit , getrennt)
Umwandlung eines uniexpoch-Zeitstempels (Zahl) in ein DateTime
Das folgende Beispiel soll darstellen, wie die SQL-Abfragen, die über das Plug-in angelegt wurden, im Rahmen der Arbeit mit SimAssist verwendet werden können.
Dazu sollen exemplarisch Daten in einer Tabelle angezeigt werden, die durch eine zuvor erstelle SQL-Abfrage spezifiziert wurden.
Fügen Sie das Plug-in Tabelle Ihrem Projekt hinzu und aktivieren Sie unter Daten den Tab Abfragen. Dort sind sämtliche SQL-Abfragen, die Sie bereits erstellt haben, aufgelistet (vgl. Abbildung 6).
Über die Liste der bestehenden Abfragen ist es auch möglich, bereits im Projekt verwendete Abfragen anzuzeigen. Details dazu finden sich im Kapitel Projektdaten im Abschnitt 1. Aufbau des Datenbereiches.
Ziehen Sie die gewünschte SQL-Abfrage per Drag&Drop in den Zielbereich des Plug-ins Tabelle. Anschließend erscheinen in der Tabelle nur genau die Daten, die auf Ihre SQL-Abfrage passen (siehe Abbildung 7).
Abbildung 6 - Abfragen als Datenquelle wählen |
Abbildung 7 - SQL-Abfrage in einer Tabelle dargestellt |
Sie können erstellte SQL-Abfragen bequem exportieren.
Markieren Sie hierzu die gewünschte SQL-Abfrage in der Übersicht und klicken Sie im Plug-in-Menü entweder auf Als Datei oder In die Zwischenablage.
Bei Klick auf den Button Als Datei werden die markierten SQL Ausdrücke in eine *.TXT-Datei gespeichert.
Bei Klick auf den Button In die Zwischenablage werden die markierten SQL Ausdrücke in der Windows-Zwischenablage temporär gespeichert und Sie können diese an beliebiger Stelle per Kontextmenü oder alternativ durch die Tastenkombination [STRG] + [V] einfügen.
Unter Optionen im Hauptmenü von SimAssist können Sie plug-in-spezifische Einstellungen vornehmen (siehe Kapitel Optionen).
Für das Plug-in SQL Ausdrücke stehen folgende Einstellungen zur Verfügung:
Option |
Beschreibung |
Schriftgröße |
|
Schriftgröße der SQL-Abfrage |
Schriftgröße innerhalb des SQL Ausdruck Editors in pt. |
Variablen |
|
Farbe der Variablen |
Legt eine Farbe fest, mit der Variablen markiert werden |
Wie bereits mehrfach erwähnt, stellen SQL-Abfragen eine effiziente Möglichkeit dar, Datenabfragen noch spezifischer zu gestalteten.
Dieser Vorteil zeigt sich insbesondere daran, dass mittels SQL-Abfragen innerhalb von Datenabfragen neue Spalten mit zusätzlicher Information erzeugt werden können – ein deutlicher Mehrwert für den Anwender.
Um dies detaillierter zu erläutern, soll das folgende Beispiel dienen.
Schritt 1
Auf Abbildung 8 ist eine Tabelle zu sehen, die einen stark vereinfachten Datensatz, welcher Informationen zu Statuspunktmeldungen enthält, darstellt.
Abbildung 8 - Statuspunktmeldungen
Schritt 2
Die Daten in der Tabelle zeigen mehrere Meldepunkte sowie die Anzahl der Objekte, welche zwischen den jeweiligen Meldepunkten innerhalb eines Tages registriert werden.
Nun wäre es jedoch auch von großem Interesse, wie viele Objekte nicht nur innerhalb eines Tages, sondern auch innerhalb einer Stunde die Meldepunkte passieren.
Um diese zusätzliche Information generieren zu können, sind nun SQL-Abfragen, welche über das Plug-in erstellt werden können, von großem Wert.
Folgende SQL-Abfrage generiert zusätzlich zur auf Abbildung 6 ersichtlichen Tabelle eine Spalte, welche die gewünschte Zusatzinformation der stündlichen Meldungen enthält (siehe Abbildung 9).
Konkret wird über das SQL-Schlüsselwort AS eine neue Spalte erstellt, welche die auf einen gesamten Tag bezogene Anzahl an Statuspunktmeldungen durch 24 teilt und damit eine stündliche Anzahl errechnet.
Mittels ROUND wird das Ergebnis auf 2 Nachkommastellen gerundet.
Abbildung 9 - SQL-Abfrage
Schritt 3
Wird diese SQL-Abfrage nun in einem Tabellen-Plug-in angewendet, erscheint die mittels SQL-Abfrage hinzugefügte Spalte.
Der Benutzer von SimAssist kann auf diese zusätzliche Information zugreifen und damit effizienter Arbeiten (siehe Abbildung 10).
Auf diese Art und Weise können weitreichende und sehr spezielle Datenabfragen generiert werden, welche in den einzelnen Plug-ins zur Anwendung kommen können.
Abbildung 10 - Neue Spalte in der Tabelle
© SimPlan AG - AG Hanau HRB 6845 - info@simplan.de - www.simplan.de