So arbeiten Sie mit SQL-Ordnern
Product line
Standard
|Expert
Operating mode
CLOUD ABO
|ON-PREMISES
Modules
Services & CRM
Budget & Phases
Purchases
Resource Planning
Business Intelligence
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:
|
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.  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:
Handelt es sich um ein Suchfeld vom Typ Boolean (Wahr/Falsch), ist der Standardwert wie folgt einzugeben:
|
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:
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:
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.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:
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:
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).