SQL-Ordner

SQL-Ordner sind empfehlenswert, wenn aus einer grossen Datenmenge bestimmte Objekte aufgrund einer einfachen Bedingung selektiert werden sollen. Im Unterschied zum Expression-Ordner erfolgt die Selektion der Daten durch den Datenbankserver, was in den meisten Fällen schneller ist. Der Nachteil von SQL-Ordnern ist, dass sie nicht automatisch auf Änderungen von Daten reagieren und dass die Flexibiliät der Abfragen gegenüber Expression-Ordnern (mit OCL) eingschränkt ist.

Ein Beispiele für einen SQL-Ordner ist der Ordner Adressen in den Stammdaten von Vertec. Wenn Sie diesen Ordner anwählen, sehen Sie oberhalb der Liste den Such-Dialog, in dem Sie bestimmte Kriterien angeben können. Im Ordner erscheinen dann nur die Einträge, die diesen Kriterien entsprechen.

Die Abfrage für einen SQL-Ordner besteht aus einer Bezeichnung und einer SQL-Bedingung. Die SQL-Bedingung muss ein gültiger SQL-Ausdruck sein, der einen Boolean Wert ergibt. Zur Auswertung wird die SQL-Bedingung als WHERE Bedingung in einem SQL-SELECT-Kommando verwendet. Die Bedingung (whereAttributes) kann maximal 8000 Zeichen umfassen.

Der Eigenschaften Dialog eines SQL-Ordners erlaubt eine dialoggestützte Bearbeitung der SQL-Abfragen. Klicken Sie mit der rechten Maustaste auf den Ordner, in dem Sie den SQL-Ordner erzeugen wollen, und wählen Sie Neu > SQL-Ordner.

Klicken Sie im SQL-Ordner-Dialog auf Abfragen definieren.... Folgendes Fenster erscheint:

Für einfache Abfragen können Sie nun im Feld SQL die entsprechende SQL-Expression eingeben. Zur Eingabe des Werts erscheint so ein normales Textfeld.

Die meisten Datenbankfelder sind identisch mit den in OCL verwendeten Attribut-Namen, es gibt aber im Falle von berechneten Attributen und Verknüpfungen Unterschiede. Eine Sammlung von SQL-Ordner-Beispielen finden Sie im Artikel SQL-Expressions für SQL-Ordner.

Möchten Sie mehrere Felder gleichzeitig für eine Suche verwenden oder benötigen Sie ein Feld eines anderen Typs (z.B. ein Datumsfeld), können Sie Suchfelder definieren. Pro Abfrage sind maximal 9 Felder zugelassen.

Über den Button + rechts unter Felder wird ein neues Suchfeld eingefügt und benannt. Ein Suchfeld wird wie folgt aufgebaut:

Bezeichnung	Das Suchfeld wird im Abfragedialog mit dieser Bezeichnung angezeigt. Es soll also ersichtlich sein, nach was gesucht wird.
Feldtyp	Folgende Typen von Suchfeldern werden unterstützt: Zeichen: Normales Textsuchfeld. Zahl: Suchfeld für einen numerischen Wert. Überprüft, ob es sich um einen gültigen numerischen Wert handelt. Datum: Datumssuchfeld mit Datepicker zur Datumsauswahl. Wahr / Falsch: Checkbox für eine Boolean-Abfrage. Auswahl: Zeigt eine Dropdown-Liste an, aus der ein Wert ausgewählt werden kann. Sobald dieser Typ gesetzt ist, wird gleich darunter ein Feld für die Expression eingeblendet. Die Expression muss als Resultat eine Liste von Strings zurückgeben. Auf der Oberfläche wird dann ein Auswahlfeld angezeigt, aus welchem der gewünschte Wert ausgewählt werden kann. Als Standard wird der unter Defaultwert angegebene Eintrag angezeigt, falls einer definiert ist. Falls ein Defaultwert definiert ist, wird standardmässig dieser angezeigt. Falls der angegebene Defaultwert nicht in der Auswahlliste vorhanden ist oder es keinen Defaultwert hat, wird der erste Eintrag der Auswahlliste genommen.
Defaultwert	Hier können Sie mittels einer OCL-Expression einen Standardwert eingeben. Dieser steht dann bereits im Feld, wenn der Benutzer die Abfrage aufruft, kann aber geändert werden. Wenn man den OCL-Expression-Editor öffnet, ist als Basisklasse immer Projektbearbeiter angegeben, auch wenn andere Objekte abgefragt werden. Hierbei handelt es sich um den eingeloggten Benutzer. Um als Defaultwert also den eingeloggten Benutzer zu verwenden, kann hier die Expression self.asstring eingegeben werden. Handelt es sich um ein Suchfeld mit dem Feldtyp Datum, erscheint zusätzlich eine Drop-Down-Liste, aus der Sie einen der folgenden Standardwerte auswählen können: Kein Datum Heutiges Datum Erster des Monats Letzter des Monats Erster des letzten Monats Letzter des letzten Monats Handelt es sich um ein Suchfeld vom Typ Boolean (Wahr/Falsch), ist der Standardwert wie folgt einzugeben: Defaultwert TRUE: Wert 1 als Zahl eintragen. Defaultwert FALSE: Nichts eintragen.

Auf der Oberfläche erscheinen die Felder dann bei der Abfrage zur Auswahl:

Die Ordner-Suche kann ab Vertec 6.2 auch unmittelbar ausgeführt werden, indem im Abfrage-Dialog die Sofort Suche aktiviert wird. Damit wird die Suche beim Anzeigen des Ordners mit den Standard-Suchparametern (z.B. der angemeldete Benutzer) direkt ausgeführt, ohne dass extra noch auf Suchen geklickt werden muss.

Weitere Suchen können wie gewohnt durch Ändern der Suchparameter und mittels Suchbutton erfolgen.

Damit auf der Oberfläche der Suchdialog erscheint, muss die Option Suchdialog anzeigen in den SQL-Ordner Konfiguration angeklickt sein. Wird kein Suchdialog angezeigt, dann wird für die Berechnung des Ordnerinhalts immer die erste Abfrage verwendet.

Ist diese Option eingeschaltet, hat es neben dem Suchen-Button einen Button Alle anzeigen. Ein Klick auf diesen Button zeigt alle Einträge an, ohne Rücksicht auf Abfragen oder Kriterien.

Bei Projekten und Adressen wird standardmässig eine Checkbox Nur aktive angezeigt.

Ob das Häkchen standardmässig gesetzt ist oder nicht, kann mit der Systemeinstellung Allgemein > Deaktivierte Einträge in Suchdialogen berücksichtigen gesteuert werden.

Ab Vertec 6.3 kann diese Option auch ausgeblendet werden. Im Abfrage-Definitions-Dialog gibt es dafür eine Option 'Nur aktive' ausblenden:

Dies hat folgende Auswirkungen:

• Die Checkbox Nur aktive wird auf der Oberfläche nicht mehr angezeigt.
• Die Suche erfolgt bei ausgeblendeter Checkbox immer in allen Einträgen, also in aktiven und in inaktiven. Ist die Option ausgeblendet, wird die oben genannte Systemeinstellung Deaktivierte Einträge in Such-Dialogen berücksichtigen nicht beachtet.

Es können auch mehrere Abfragen pro Ordner definiert werden, auch diese nach Bedarf mit mehreren Feldern. Um eine neue Abfrage zu erzeugen, klicken Sie auf das + links bei den Abfragen.

Ist der Ordner so konfiguriert, dann stehen die verschiedenen Abfragen als Reiter oberhalb des Suchdialogs zur Verfügung:

Die Felder werden pro Abfrage der Reihe nach abgefragt, von oben nach unten. Die Variablen sind jeweils \1 für das erste Feld, \2 für das zweite Feld, \3 für das dritte Feld etc.

Eine Suche nach Datumsintervall lautet beispielsweise wie folgt:

datum >= '\1' and datum <= '\2'

Sie können diese Abfrage auch so definieren, dass nur entweder ein Startdatum oder ein Enddatum eingegeben werden kann und die Abfrage trotzdem funktioniert. In diesem Fall muss sie lauten:

(('\1'='') or (CreationDateTime>'\1')) and (('\2'='') or (CreationDateTime<'\2'))

Erweitert man eine Abfrage durch eine Boolean-Abfrage, gilt es, folgendes zu beachten:

Angenommen, in einer Abfrage werden alle Rechnungen abgefragt. Was passiert nun, wenn wir dort eine Wahr/Falsch-Abfrage hinzufügen, um abzufragen, ob offene oder verrechnete Rechnungen berücksichtigt werden sollen?

Als Beispiel dient eine Abfrage nach Datum und Nummer. Diese lautet:

datum < '\1' AND (nummer like '\2%')

Da nun die Abfrage um das Wahr/Falsch- Feld erweitert werden muss, bedeutet das, dass sich der Sinn der ursprünglichen Abfrage ändert. Fügt man bei der Rechnungsabfrage z.B. eine Checkbox "nur verrechnete" hinzu, muss die bisherige Abfrage beispielsweise wie folgt ergänzt werden:

... AND verrechnet=\3

Das bedeutet, dass, wenn nichts angegeben wird, nun nur nach offenen gesucht wird!

Um dies zu umgehen, kann man die Abfrage mit einem CASE WHEN ergänzen:

AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)

Wenn Parameter 3 (unsere Checkbox) gleich 1 ist (d.h. sie ist angewählt), dann vergleichen wir das verrechnet Feld mit 1 (nur die verrechneten), wenn Parameter 2 gleich 0 ist (d.h. abgewählt), vergleichen wir verrechnet mit verrechnet, d.h. es werden alle gefunden.

Die ganze Abfrage lautet in dem Fall:

datum < '\1' AND (nummer like '\2%') AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)

Wichtige Informationen:

• Checkboxen haben immer einen Wert. Sie sind entweder True oder False. Bei Textfeldern erscheint eine Meldung, wenn nichts eingegeben wird. Wenn eine Checkbox als Suchfeld definiert ist, dann wird eine solche Meldung nicht erscheinen, auch wenn in allen Textfeldern nichts drin steht, weil eine nicht aktive Checkbox den Wert False hat und die Suche damit gestartet wird.
• Bei Abfragen von Adressen (Klasse Adresseintrag) und Projekten (Klasse Projekt) sollte keine Checkbox verwendet werden, um den Status Aktiv abzufragen, da es bei diesen Klassen standardmässig eine Checkbox Auch deaktivierte durchsuchen gibt, welche dies bereits implementiert (siehe Abschnitt Auch deaktivierte durchsuchen weiter oben).

Ab Version 6.1 ist es in den neuen Apps möglich, in den Spalten-Expression auf die eingegebenen Such-Parameter zuzugreifen. Das ermöglicht es zum Beispiel, Datums-Intervalle, die für die SQL Abfrage verwendet wurden, auch in den OCL-Expressions für die Listenspalten zu verwenden.

Für jedes Suchfeld im SQL-Ordner Dialog wird eine OCL Variable angelegt. Die Namenskonvention lehnt sich an die OCL Variablen in Word-Reports an: var<Feldname>.

Bei der Bildung des Variablennamens aufgrund des Feldnamens werden nur Buchstaben und Zahlen aus dem ASCII Bereich berücksichtigt. Alle anderen Zeichen (z.B. Leerzeichen, Bindestriche, Umlaute etc.) werden ausgefiltert und für den Variablennamen nicht berücksichtigt.

Ein Feld mit Bezeichnung Bis Datum führt also zu einer Variablen varBisDatum.

Die angelegten Variablen erhalten gemäss Typ des Suchfeldes einen entsprechenden OCL Typ:

• String: String
• Boolean: Boolean
• Date: DateTime
• Number: Float

Man möchte zum Beispiel eine Liste der Projekte mit offenen Leistungen bis Stichtag. Das ist auch bisher schon möglich mit der SQL-Expression bold_id IN (SELECT projekt FROM offeneleistung WHERE datum<='\1').

Nun möchte man auch noch das aufgelaufene Honorar bis Stichtag in einer Spalte sehen, und das geht neu mit den neuen Spaltenvariablen:

Hier im Beispiel haben die drei Spalten ab Stichdatum folgende Expressions:

• Stichdatum: varStichdatum
• Offenes Honorar bis Stichdatum: offeneLeistungen->select(datum<=varStichdatum).wertext->sum
• Total offene Leistungen: summeOffeneLeistungen

Für Felder ohne Namen (das ist der Standard bei SQL-Ordnern mit nur einer Abfrage, ohne extra Suchfeld) wird als Feld-Name Search verwendet. Die resultierende Variable heisst dann varSearch. Zum Beispiel der Ordner Rechnungen (Suche). In diesem Fall lautet die Spaltenexpression: rechnungen->select(nummer=varSearch).

Die Verwendung von Suchfeld-Variablen funktioniert nur bei SQL-Ordnern mit nur einer Abfrage. Bei SQL-Ordnern mit mehreren Abfragen können die Variablen falsche Werte (aus den anderen Abfragen) anzeigen oder Fehlermeldungen erscheinen (falls Variablen verwendet werden, die nicht in allen Abfragen vorkommen).