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.

Einen SQL-Ordner erzeugen

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:

sqlabfrage.gif

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.

In mehreren Feldern suchen

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.

Ü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.

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.

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:

Mehrere Abfragen pro SQL-Ordner

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:

Suchdialog anzeigen

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.

"Alle anzeigen" erlauben

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.

Auch deaktivierte durchsuchen

Bei Projekten und Adressen wird standardmässig eine Checkbox Auch deaktivierte durchsuchen angezeigt.

Unter Systemeinstellung Allgemein > Deaktivierte Einträge in Suchdialogen berücksichtigen kann diese Anzeige gesteuert werden.

SQL-Abfrage erstellen

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

Abfrage erweitern durch eine Boolean-Abfrage

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

Zugriff auf die Suchparameter in den Spalten-OCL-Expressions

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

Beispiel

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

SQL-Abfragen ohne Suchfelder

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

Hinweis

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

Die Unterstützung für Suchfeld-Variablen wurde nur in den neuen Apps implementiert. In der Classic App führen Spalten-Expressions, welche solche Variablen verwenden, zu Laufzeitfehlern.


23.05.2003 | 16.11.2016: Release 6.1: Zugriff auf die Suchparameter in den Spalten-OCL-Expressions sowie Screenshots von den neuen Dialogen eingefügt.
Produktlinien: Standard, Expert
Module: Leistung & CRM