SQL-Expressions für SQL-Ordner

Vergleiche

|| Operator / + Operator

Cast-Operator

Abfrage des eingeloggten Benutzers

Abfrage von normalen Feldern

Abfrage von Membern

Abfrage von Blob-Feldern

Abfrage von Datumsfeldern

Abfrage von Zusatzfeldern
Abfrage von Custom-Links

Abfrage von Generic-Links

Abfragebeispiele:

Rechnungen (Klasse: Rechnung)

Leistungen (Klasse: Leistung)

Projekte (Klasse: Projekt)

Phasen (Klasse: Projektphase)

Bearbeiter

Aktivitäten

Vergleiche

Die Vergleiche in SQL erfolgen auf drei Arten

  • Exakter Vergleich: ='\1'. Der gesamte eingegebene Wert muss mit dem gesuchten Wert übereinstimmen. Wird vor allem für Zahlen verwendet.
  • Zeichenvergleich, Anfang: like '\1%'. Der eingegebene Wert entspricht dem Anfang des gesuchten Wertes.
  • Zeichenvergleich, Teil: like '%\1%'. Der eingegebene Wert entspricht einem Teil des gesuchten Wertes.

Die Ziffer 1 ist der Platzhalter die Abfrage, die vom Benutzer mittels Dialog abgefragt wird. Sind mehrere Suchfelder definiert, werden die Ziffern in der Reihenfolge 1,2,3... verwendet in der entsprechenden Reihenfolge der Suchfelder. Wenn Sie ohne Abfragedialog arbeiten, verwenden Sie statt des Platzhalters einfach Ihr Kriterium, das Sie abfragen wollen.

|| Operator (Firebird)

Der || Operator wird im Firebird verwendet, um Textelemente zusammenzuhängen. Jeweils das Element vor und nach dem || werden zusammengehängt.

extract(month from datum) || '.' || extract(year from datum)='\1'

Im obigen Beispiel wird zuerst der extrahierte Monat (z.B. 08) genommen, mit einem Punkt zusammengehängt und dann das extrahierte Jahr (z.B. 2006) ebenfalls angehängt. Das ergibt dann einen String in der Form Monat.Jahr (z.B. 08.2006).

+ Operator (MS SQL)

Der + Operator wird auf MS SQL-Server verwendet, um Textelemente zusammenzuhängen. Jeweils das Element vor und nach dem + werden zusammengehängt.

(cast(datepart(month,datum) as varchar)+'.'+cast(datepart(year,datum) as varchar)= '\1')

Im obigen Beispiel wird zuerst der extrahierte Monat (z.B. 08) genommen, mit einem Punkt zusammengehängt und dann das extrahierte Jahr (z.B. 2006) ebenfalls angehängt. Das ergibt dann einen String in der Form Monat.Jahr (z.B. 08.2006).

Cast-Operator

Der Cast-Operator wird verwendet, um einen Wert in einen bestimmten Datentyp umzuwandeln. Das wird vor allem dann verwendet, wenn mit dem Resultat weitergearbeitet werden muss, z.B. mit Zahlen gerechnet oder zu einem Datum einen Wert dazugezählt. Die Syntax lautet wie folgt:

CAST (value AS datatype)

Geläufige Datentypen sind:

  • timestamp (oder date)
  • varchar
  • integer
  • numeric

String in einen Integer umwandeln

Der cast-Operator kann dazu verwendet werden, einen String in einen numerischen Wert umzuwandeln, z.B. eine PLZ auf einer Adresse:

cast(standardplz as integer)

Das geht aber nur, wenn der Inhalt wirklich nur nummerische Werte enthält, sonst gibt's eine Fehlermeldung. Deshalb sollte man gleichzeitig auf numerischen Inhalt prüfen. Das ist im Moment nur mit MS SQL Server möglich, für Firebird existiert leider keine isnumeric Funktion. Beispiel MS SQL Server:

(isnumeric(standardplz)=1 and cast(standardplz as integer) >= 8000)

Abfrage des eingeloggten Benutzers

Ab Version 5.3.0.48. Mit dem String \login_id kann innerhalb eines SQL Ordners auf die ID des Logins zugegriffen werden. So kann man zum Beispiel einen SQL-Ordner machen, mit dem der Benutzer in seinen betreuten Adressen suchen kann:

KlasseSQL-Expression
Adresseintrag (Name like '%\1%' OR Alias like '%\1%') AND betreuer=\login_id

Anmerkung: Soll der Benutzer wirklich nur in seinen eigenen Adressen suchen können, muss die Option "alle anzeigen erlauben" ausgeschaltet werden.

Abfrage von normalen Feldern

Wenn normale Felder abgefragt werden, geht das am Einfachsten über den Button Feld einfügen... im SQL-Dialog. Normale Felder werden mit einem der obengenannten Vergleiche abgefragt. Beispiel:

Betreffend like '%\1%'

Abfrage von Membern

Als Beispiel möchten wir einen SQL-Ordner erstellen, der alle offenen Leistungen eines Mitarbeiters anzeigt. Nach diesem Mitarbeiter soll gesucht werden.

  • Klasse: OffeneLeistung
  • Abfrage-Bezeichnung (frei): Bearbeiter
  • SQL: Bearbeiter in (SELECT Bold_ID from Projektbearbeiter WHERE Name like '%\1%')

Abfrage von Blob-Feldern

Firebird

BLOB's werden im Vertec für Felder verwendet, die langen Text enthalten, z.B. der Text bei der Aktivität. Möchte man solche Blob-Felder via SQL-Ordner durchsuchen, kann man folgendes Statement verwenden (funktioniert nur für Firebird):

  • Klasse: Aktivitaet
  • Abfrage-Bezeichnung (frei): Text
  • SQL: text containing '\1'

Das Statement muss von Hand eingefügt werden, da der Auswahldialog das 'containing' nicht standardmässig anbietet. Die Suche ist Case-Insensitive, das heisst, es findet Gross-, Kleinbuchstaben und beliebige Kombinationen von beiden.

MS SQL-Server

In MS-SQL-Server können solche Felder erst ab Vertec Version 5.6 durchsucht werden. Dabei werden nicht BLOB, sondern VARCHAR(MAX) Felder verwendet.

Das Statement lautet wie folgt:

  • Klasse: Aktivitaet
  • Abfrage-Bezeichnung (frei): Text
  • SQL: text like '%\1%'

Diese Suche ist Case-Insensitive, das heisst, es findet Gross-, Kleinbuchstaben und beliebige Kombinationen von beiden.

Abfrage von Datumsfeldern

Normale Datumsfelder, wie sie zum Beispiel auf der Rechnung, bei Leistungen etc. vorkommen, können wie folgt abgefragt werden:

  • Datum='\1'

  • Datum<'\1'

  • Datum>'\1'

Heutiges Datum

Firebird

Mit Firebird kommt man auf das heutige Datum via cast('TODAY' as timestamp). So kann man z.B. herausfinden, welche Einträge seit X Tagen erfasst wurden:

CreationDateTime>=cast('TODAY' as timestamp)-\1

MS SQL-Server

Im MS SQL Server kommt man auf das heutige Datum via GETDATE(). So kann man z.B. herausfinden, welche Einträge seit X Tagen erfasst wurden:

CreationDateTime>=GETDATE()-\1

Extrahieren von Tag, Monat, Jahr

Firebird

Syntax: extract(element from feld)

  • element: Die wichtigsten, vordefinierten Elemente sind:
    • YEAR
    • MONTH
    • DAY
  • feld: Datumsfeld in der Datenbank, aus welchem das entsprechende Element extrahiert werden soll.
extract(year from datum)=\1

Im obigen Beispiel wird year aus dem Feld datum extrahiert und mit dem eingegebenen Jahr verglichen (= '\1').

MS SQL-Server

Syntax: datepart(element, feld)

  • element: Die wichtigsten, vordefinierten Elemente sind:
    • YEAR
    • MONTH
    • DAY
  • feld: Datumsfeld in der Datenbank, aus welchem das entsprechende Element extrahiert werden soll.

cast(datepart(year,datum) as varchar) = '\1'

Im obigen Beispiel wird year aus dem Feld datum extrahiert und mit dem eingegebenen Jahr verglichen (= '\1').

Einschränkung: Bei einer Suche nach Objekten eines Monats, z.B. Rechnungen vom Juli 07, muss die Eingabe 7.2007 sein und darf NICHT 07.2007 sein

Datumsdifferenz

Firebird

Keine spezielle Funktion für eine Datumsdifferenz. Wenn man aber beispielsweise die Leistungen seit X Tagen bis heute suchen will, wobei X vom User eingegeben wird, kann man mit dem heutigen Datum wie folgt arbeiten:

datum>=cast('TODAY' as date)-\1

MS SQL-Server

DATEDIFF(datepart , startdate , enddate)

Beispiel: Leistungen seit X Tagen bis heute; X kann vom User eingegeben werden:

(DATEDIFF(day, datum, getdate()) < \1) and (datum <= getdate())

Datumsfelder mit Zeitteil (CreationDateTime, ModifiedDateTime)

Daneben gibt es in Vertec spezielle Datumsfelder, welche zusätzlich noch einen Zeitteil gespeichert haben. Das sind zum Beispiel die CreationDateTime oder ModifiedDateTime, die auf jedem Eintrag gespeichert werden.

Einen genauen Datumsvergleich kann man hier nicht machen, weil ein Datum mit Zeitteil stets grösser ist als dasselbe Datum ohne Zeitteil. Dies kann man wie folgt umgehen: Man zählt beim eingegebenen Bis-Datum einen Tag dazu und macht einen < Vergleich. Beim Von-Datum muss man nichts dazuzählen. So erhält man als Resultat einen einzelnen Tag (wenn man wissen möchte, welche Einträge an welchem Tag erzeugt oder verändert wurden). Das Datum wird vom Benutzer abgefragt.

Beispiel Firebird

Einzelner Tag

CreationDateTime >= '\1' AND CreationDateTime < '\1'+1

Datumsintervall

CreationDateTime >= '\1' AND CreationDateTime < '\2'+1

Beispiel MS SQL-Server

Einzelner Tag

creationdatetime>="\1" and creationdatetime<cast("\1" as datetime)+1

Datumsintervall

creationdatetime>="\1" and creationdatetime<cast("\2" as datetime)+1

Mit folgendem Statement kann man ausserdem abfragen, welche Einträge nicht an einem bestimmten Datum erfasst worden sind (ist nützlich z.B. bei Migrationen, wenn man die nicht migrierten Einträge anzeigen möchte):

Beispiel Firebird

CreationDateTime < '\1' OR CreationDateTime >= '\1'+1

Beispiel MS SQL-Server

creationdatetime<"\1" or creationdatetime>=cast("\1" as datetime)+1

Abfrage von Zusatzfeldern

Die Abfrage von Zusatzfeldern via SQL-Ordner erfolgt je nach Zusatzfeld-Typ nach folgendem Schema:

Firebird und MS SQL-Server

bold_id IN (SELECT usereintrag FROM zusatzfeld WHERE
metazusatzfeld=[Id des Zusatzfeldes] AND <Ausdruck><=Vergleich>))

Erklärung

Diese OCL Expression kann für alle Zusatzfelder gleich verwendet werden. Die Teile in <eckigen Klammern> müssen jeweils angepasst werden:

  • <Id des Zusatzfeldes>: Dabei handelt es sich um die interne ID des Zusatzfeldes, das man abfragen möchte:

  • <Ausdruck>: Je nach Feld-Typ muss die Abfrage anders formuliert werden. Eine vollständige Auflistung der Abfragen je nach Typ finden Sie hier anschliessend.
  • <Vergleich>: Die Vergleiche können auf drei Arten erfolgen: =\1, like "\1%" und like "%\1%". Der Platzhalter \1 repräsentiert jeweils den vom Benutzer im Suchdialog eingegeben Wert. Wenn Sie einen SQL-Ordner ohne Suchdialog anzeigen, müssen Sie hier einen konkreten Wert eingeben. Wann Sie welche Art von Vergleich verwenden müssen, finden Sie weiter oben in diesem Artikel im Abschnitt Vergleiche.

Übersicht

Zusatzfeld-typ

Abfrage

Zeichen

  • Ausdruck: Wert
  • Vergleich: like "\1%" oder like "%\1%"

Mit Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND Wert like "\1%")

Ohne Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND Wert like "AQA%")

Wahr, Falsch

  • Ausdruck: WertBoolean
  • Vergleich: 0 für Falsch, 1 für Wahr

Ohne Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertBoolean<>0)

Ganzzahl
Minuten

  • Ausdruck: WertInteger
  • Vergleich: = \1

Mit Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertInteger =\1)

Ohne Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertInteger >= 1000)

Festkommazahl

  • Ausdruck: WertCurrency
  • Vergleich: =\1

Mit Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertCurrency =\1)

Ohne Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertCurrency = 1350)

Datum

  • Ausdruck: WertDatum
  • Vergleich: ="\1", >"\1", <"\1"

Mit Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertDatum ="\1")

Ohne Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertDatum = "15.03.2014")

Text

  • Ausdruck: WertBlob
  • Vergleich: containing '\1'

Mit Suchdialog:

Firebird:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertBlob containing '\1')

MS-SQL-Server (ab Vertec Version 5.6):

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 16311 AND WertBlob LIKE "%\1%")

Ohne Suchdialog:

Firebird:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertBlob containing 'Newsletter')

MS-SQL-Server (ab Vertec Version 5.6):

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertBlob LIKE "%Newsletter%")

Bild

  • Ausdruck: WertBlob
  • Vergleich: Bei einem Bild-Feld kann nur abgefragt werden, ob ein Bild zugeordnet ist oder nicht. Das kann über einen String-Vergleich erfolgen (ist leer oder ist nicht leer).

Ohne Suchdialog:

Firebird:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertBlob <> "")

MS SQL-Server (ab Vertec Version 5.6):

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 8790353 AND not WertBlob is null)

Objekt

  • Ausdruck: WertObject
  • Vergleich: =\1

Wird ein Objekt zugeordnet, enthält das Feld die ID dieses Objekts.

Beispiel: Als Kundenbetreuer kann ein Projektbearbeiter zugeordnet werden. Im Suchdialog kann über das Kürzel alle Adressen angezeigt werden, die von diesem Projektbearbeiter betreut werden.

Mit Suchdialog:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertObject in (SELECT bold_id from Projektbearbeiter where kuerzel = '\1'))

Wird beispielsweise eine Zusatzklasse als Objekt angefügt, lautet das Statement wie folgt:

bold_id in (SELECT userEintrag FROM Zusatzfeld WHERE MetaZusatzfeld = 12345 AND WertObject in (SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=19950 AND wert like "%\1%"))

Anmerkung zu den entsprechenden Bold_ID's (12345 und 19950): Das erste Zusatzfeld ist dasjenige auf der gesuchten Klasse. Wenn man mit dem Ordern z.B. nach Projekten sucht, ist es das entsprechende Zusatzfeld auf der Klasse Projekt. Das zweite angegebe Zusatzfeld ist dasjenige der Zusatzklasse, welches den gesuchten Begriff enthält. Z.B. wäre es denkbar, dass man den Inhalt von Archivschachteln mit Projekten verknüpft. Dafür kann man eine Zusatzklasse eröffnen und dazugehörende Zusatzfelder wie z.B. Schachtelnummer und Schachtelinhalt etc. Die Bold_ID des gewünschten Zusatzfeldes gibt man dann im Statement an.

Die Bold_ID eines Objekts findet man unter Rechtsklick auf das Objekt > Eigenschaften > Interne Id.

Auswahlfeld

  • Ausdruck: WertInteger
  • Vergleich: =\1

Als Beispiel ein Zusatzfeld Region (Auswahl) auf Projekt. Als Auswahl-Expression steht: "CH", "DE", "AU" zur Auswahl.

Nach dieser Auswahl soll im SQL-Ordner gesucht werden.

Ohne Suchdialog:

bold_id in (select usereintrag from zusatzfeld where wertinteger = 0 and MetaZusatzfeld = 12345)

Die Zahl 0 steht für die Position im Auswahlmenü des Zusatzfeldes und kann entsprechend angepasst werden. (von 0 beginnend). Diese Abfrage ergibt also alle Projekte der Region "CH"

Mit Suchdialog:

Es kann auch eine bestimmte Region vom User abgefragt werden. Man muss dann sowohl die Eingabe wie auch die Auswahl überprüfen. Das geschieht für obiges Beispiel wie folgt:

bold_id in (select usereintrag from zusatzfeld WHERE MetaZusatzfeld = 12345 AND 
((('\1'='CH') and (WertInteger=0)) or (('\1'='DE') and (WertInteger=1)) or (('\1'='AU') and (WertInteger=2))))

Abfrage von Custom-Links

Als Beispiel nehmen wir einen Custom-Link-Typ Verwaltungsräte-VRMandate (Person-Firma). Sie möchten nun einen SQL-Ordner erstellen, bei dem Sie VRMandate nach Verwaltungsräten suchen können. Erstellen Sie also einen SQL-Ordner und füllen Sie ihn wie folgt aus:

  • Klasse: Firma
  • Abfrage-Bezeichnung (Bsp): Verwaltungsrat
  • SQL:

    BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE FromCustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like "\1%") AND Linktyp = 28987)

  • Wenn der Linktyp VRMandate-Verwaltungsräte heisst, die Link-Rollen also umgekehrt definiert sind, lautet die Expression stattdessen:
    BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE CustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like "\1%") AND Linktyp = 28987)

    Diese Abfrage liefert alle Firmen zurück, mit denen die abgefragte Person verbunden ist. Die Bold_ID des entsprechenden Linktyps, im Beispiel 28987, eruieren Sie in den Eigenschaften des Linktyps (Menüb Aktionen > Eigenschaften):

    CustomLinks von normalen Klassen zu Zusatzklassen

    Die Abfrage von CustomLinks von normalen Klassen zu Zusatzklassen und umgekehrt erfolgt genau gleich. Etwas speziell ist die zusätzliche Abfrage von Zusatzfeldern auf solchen Zusatzklassen.

    Als Beispiel nehmen wir eine Zusatzklasse12. Mit dieser werden installierte Versionen hinterlegt. Über einen CustomLink wird die entsprechende Version beim Kunden (Adresseintrag) hinterlegt. Der entsprechende CustomLink sieht wie folgt aus:

    Will man alle Kundenadressen anzeigen, die irgendeine Version haben, dann lautet die Abfrage:

    BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE Linktyp = 55592)

    Will man alle Objekte der Klasse Zusatzklasse12 anzeigen, die irgendeinen Kunden haben, dann lautet die Abfrage:

    BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE Linktyp = 55592)

    Als Nummer (im Beispiel 55592) muss die Interne ID des Linktyps angegeben werden:

    Zusätzliche Abfrage von Zusatzfeldern auf der Zusatzklasse

    Obiges Beispiel ist ja noch nicht so spannend, man möchte zum Beispiel alle Adressen finden, die eine bestimmte Version installiert haben. Dafür suchen wir zusätzlich im Zusatzfeld Version auf der Zusatzklasse (in unserem Beispiel ist das eine Ganzzahl):

    BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE ((Linktyp = 55592) and (FromCustomLinks in 
    (SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=27854 AND wertinteger=124))))

    Wenn man das Zusatzfeld einmal hat, dann kann man es gleich abfragen wie die normalen Zusatzfelder (siehe Abschnitt Abfrage von Zusatzfeldern), hier zum Beispiel mit wertinteger, weil es ein Ganzzahl-Feld ist.

    Obiges Beispiel gibt alle Adressen zurück, die die Version 124 installiert haben. Die Nummer des Metazusatzfeldes (im Beispiel 27854) entspricht der Internen ID des Zusatzfeldes Version.

    Abfrage von Generic-Links

    Wir nehmen das gleiche Beispiel wie beim Custom-Link (siehe vorherigen Abschnitt): Verwaltungsräte - VRMandate.

    • Klasse: Firma
    • Abfrage-Bezeichnung (Bsp): Verwaltungsrat
    • SQL:

      BOLD_ID in (SELECT FromContainer FROM GenericLinkContainer WHERE rolle IN (SELECT bold_id FROM linkrolle WHERE bezeichnung ='Verwaltungsräte') AND BOLD_ID in (SELECT fromLinks FROM Link WHERE Links IN (SELECT BOLD_ID FROM Adresseintrag WHERE Name like "\1%")))

    GenericLink von normalen Klassen zu Zusatzklassen

    Will man stattdessen SQL-Ordner für GenericLinks von normalen Klassen zu Zusatzklassen erstellen, lautet die Expression wie folgt.

    BOLD_ID in (SELECT FromContainer FROM GenericLinkContainer WHERE rolle IN (SELECT bold_id FROM linkrolle WHERE bezeichnung ='Rechnungsaufteilung') AND BOLD_ID in (SELECT fromLinks FROM Link WHERE Links IN (SELECT BOLD_ID FROM Zusatzklassebase)))

    Dieser Ordner findet Projekte, die über einen GenericLink mit der Zusatzklasse12 verbunden sind. Der Name der Linkrolle vom Projekt her gesehen ist in diesem Fall Rechnungsaufteilung.

    Möchte man anstelle der Projekte die Zusatzklassen-Objekte ausgeben, lautet die Expression wie folgt (Unterschiede in fetter Schrift):

    BOLD_ID  in (SELECT FromContainer FROM GenericLinkContainer WHERE rolle IN (SELECT bold_id FROM 
    linkrolle WHERE bezeichnung ='Projekt') AND BOLD_ID in (SELECT fromLinks FROM Link WHERE
    Links IN (SELECT BOLD_ID FROM Projekt)))

    Zusätzliche Abfrage von Zusatzfeldern auf der Zusatzklasse

    Grundsätzlich werden Zusatzfelder auf Zusatzklassen gleich abgefragt wie die Zusatzfelder auf ganz normalen Klassen, als Klasse gibt man einfach die entsprechende Zusatzklasse (z.B. "Zusatzklasse12") an.

    Möchte man eine Abfrage machen, die nicht alle Zusatzklassen zurückgibt, sondern nur die, die auf der Zusatzklasse in einem Zusatzfeld einen entsprechenden Wert haben, muss die Abfrage anders aufgebaut werden.

    Umgebungsbeschreibung

    Zusatzklasse: Zusatzklasse12
    Name: 'Installierte Versionen'

    Generischer Link-Typ: Zusatzklasse12 - Adresseintrag

    Von:

    Bezeichnung: Installierte Versionen
    Klasse: ZusatzKlasse12

    Nach:

    Bezeichnung: Kundenadresse
    Klasse: Adresseintrag

    Zusatzfeld auf der Zusatzklasse

    Name: Version
    Feldtyp: Ganzzahl
    Klassen: ZusatzKlasse12

    Aufbau der Abfrage

    Klasse: Adresseintrag

    BOLD_ID in (SELECT FromContainer FROM GenericLinkContainer WHERE rolle in (SELECT bold_id FROM LinkRolle
    where bezeichnung = "Installierte Versionen") and bold_id in (SELECT fromLinks FROM Link WHERE Links IN (SELECT
    BOLD_ID FROM Zusatzklassebase WHERE bold_id in (select usereintrag from zusatzfeld where metazusatzfeld =
    <Bold_ID> and wertinteger = 12345))))
    • <Bold_ID>: Hier muss die Bold_ID des Metazusatzfeldes angegeben werden. Das ist die interne ID des Zusatzfeldes:
    • wertinteger = 1234: Je nach Art des Zusatzfeldes heisst es "wertinteger" oder "wertcurrency" etc. Siehe dazu die Übersicht über die Abfrage von Zusatzfeldern weiter oben.
      Der Wert "1234" entspricht dem gesuchten Begriff und muss entsprechend angepasst werden. Stattdessen kann auch als Vergleich "=\1" eingefügt werden und Suchdialog anzeigen aktiviert werden. Dann kann der Benutzer den Suchbegriff im Suchdialog angeben.

    Abfragebeispiele

    Rechnungen (Klasse: Rechnung)

    Rechnungen des aktuellen Monats

    Suchdialog anzeigen: nein

    Firebird

    (extract (month from datum) = extract (month from cast('TODAY' as date))) and 
    (extract (year from datum) = extract (year from cast('TODAY' as date)))

    MS SQL-Server

    (cast(datepart(month, datum) as varchar) = cast(datepart(month, GETDATE() as varchar)) AND 
    (cast(datepart(year, datum) as varchar) = cast(datepart(year, GETDATE() as varchar))

    Rechnungen des letzten Monats

    Suchdialog anzeigen: nein

    Firebird

    (extract (month from datum) = extract (month from cast('TODAY' as date))-1) and 
    (extract (year from datum) = extract (year from cast('TODAY' as date)))

    MS SQL-Server

    (cast(datepart(month, datum) as integer) = cast(datepart(month, GETDATE() as integer)-1) AND 
    (cast(datepart(year, datum) as integer) = cast(datepart(year, GETDATE() as integer))

    Rechnungen nach Valutadatum

    • Wenn Systemeinstellung Bis Datum für Rechnung Valutadatum berücksichtigen auf JA

      Suchdialog anzeigen: ja

      Firebird und MSSQL-Server

      (case when xvalutadatum is null THEN (case when bis is null THEN datum else bis END) ELSE xvalutadatum END) <="\1"

    • Wenn Systemeinstellung Bis Datum für Rechnung Valutadatum berücksichtigen auf NEIN

      Suchdialog anzeigen: ja

      Firebird und MSSQL-Server

      (case when xvalutadatum is null THEN datum else xvalutadatum END) <="\1"

    Unbezahlte Rechnungen seit...

    Suchdialog anzeigen: ja

    Firebird

    (bezahlt = 0) and (datum<(cast('TODAY' as date)-"\1"))

    MS SQL-Server

    (bezahlt = 0) and (datum<(getdate()-"\1"))

    Rechnungen nach Monat.Jahr

    Suchdialog anzeigen: ja

    Firebird

    extract(month from datum) || '.' || extract(year from datum)=\1

    MS SQL-Server

    cast(datepart(month, datum) as varchar) + '.' + cast(datepart(year,datum) as varchar) = '\1'

    Rechnungen nach Projekt

    Rechnung muss zu Projekt mit bestimmtem Codemuster gehören. Der Code wird abgefragt.

    Suchdialog anzeigen: ja

    Firebird und MS SQL-Server

    projekt in (select bold_id from projekt where code like "%\1%")

    Rechnungen von Projekten eines bestimmten Typs, unter Ausschluss eines bestimmten Codes

    Im ersten Teil wird vom Projekt auf den Projekttyp zugegriffen und im zweiten Teil ist wird ein bestimmter Projektcode ausgeschlossen. Da bei diesen Abfragen das SQL not like nicht funktioniert, wird mit einer Unterabfrage der entsprechende Teil ausgeschlossen.

    Suchdialog anzeigen: nein

    projekt in (Select bold_id from projekt where projekt.typ = 18710) AND 
    projekt not in (select bold_id from projekt where code like "PD%")

    wobei die Zahl 18710 im Beispiel der internen ID (Bold_ID) des entsprechenden Projekttyps entspricht (rechte Maustaste auf den entsprechenden Typ > Eigenschaften > Interne Id); und "PD%" heisst, es werden alle Projekte mit einem Code beginnend mit PD ausgeschlossen.

    Rechnungen mit Abfrage Projektleiter

    Es sollen alle Rechnungen eines Projektleiters angezeigt werden. Im Beispiel abgefragt wird das Kürzel des Projektleiters.

    Suchdialog anzeigen: ja

    projekt in (SELECT bold_id from Projekt WHERE Projektleiter in 
    (select bold_id from Projektbearbeiter where kuerzel like "%\1%"))

    Möchten Sie nur die offenen Rechnungen eines Projektleiters anzeigen, lautet die SQL-Expression wie folgt:

    projekt in (SELECT bold_id from Projekt WHERE Projektleiter in 
    (select bold_id from Projektbearbeiter where kuerzel like "%\1%")) and verrechnet=0

    Leistungen (Klasse: Leistung)

    Leistungen nach Wochentagen

    Dieser SQL-Ordner zeigt als Resultat eine Liste aller (offenen und verrechneten) Leistungen, welche an einem bestimmten Wochentag erbracht worden sind. (Anmerkung: Falls Sie offene und verrechnete Leistungen in separaten Listen anzeigen möchten, aktivieren Sie auf dem Reiter Allgemein die Option eigene Liste für jeden Typ).

    Suchdialog anzeigen: Nein

    Firebird

    ((extract (weekday from datum))=6) or((extract (weekday from datum))=0)

    beispielsweise gibt Leistungen zurück, die entweder an einem Samstag oder Sonntag erbracht worden sind.

    Die Wochentage in der Abfrage sind folgenden Zahlen zugeordnet:

    0 = Sonntag
    1 = Montag
    2 = Dienstag
    3 = Mittwoch
    4 = Donnerstag
    5 = Freitag
    6 = Samstag

    MS SQL-Server

    (datepart(weekday,datum)=6)or((datepart(weekday,datum)=7))

    beispielsweise gibt Leistungen zurück, die entweder an einem Samstag oder Sonntag erbracht worden sind.

    Die Wochentage in der Abfrage sind folgenden Zahlen zugeordnet:

    1 = Montag
    2 = Dienstag
    3 = Mittwoch
    4 = Donnerstag
    5 = Freitag
    6 = Samstag
    7 = Sonntag

    Leistungen einer bestimmten Zeitperiode

    Dieser SQL-Ordner zeigt als Resultat eine Liste aller Leistungen, welche in einer bestimmten Zeitperiode erbracht worden sind.

    Suchdialog anzeigen: Ja

    • Leistungen eines bestimmten Tages:

      Firebird und MS SQL-Server

      datum = '\1'

      Eingabe 'dd.mm.yyyy', z.B. '01.01.2014'

    • Leistungen eines Monats:

      Firebird

      (extract(month from datum) || '.' || extract(year from datum) = \1)

      Eingabe 'mm.yyyy', z.B. '01.2014'

      MS SQL-Server

      cast(datepart(month, datum) as varchar) + '.' + cast(datepart(year,datum) as varchar) = '\1'

      Eingabe 'm.yyyy', z.B. '1.2014'

    • Leistungen eines Jahres:

      Firebird

      extract(year from datum) = \1

      Eingabe 'yyyy', z.B. '2014'

      MS SQL-Server

      cast(datepart(year , datum) as varchar) = '\1'

      Eingabe 'yyyy', z.B. '2014'

    Leistungen einer bestimmten Tätigkeit

    Dieser SQL-Ordner zeigt als Resultat eine Liste aller (offenen und verrechneten) Leistungen, welche einer bestimmten Tätigkeit zugewiesen sind. Im Abfragedialog kann die Tätigkeit angegeben werden.

    Suchdialog anzeigen: ja

    Firebird und MS SQL-Server

    typ in (select bold_id from projekteintragstyp where (bold_type=22) and (code like '\1%'))

    Anmerkung: Falls Sie offene und verrechnete Leistungen in separaten Listen anzeigen möchten, aktivieren Sie auf dem Reiter Allgemein die Option eigene Liste für jeden Typ.

    Erfasste Leistungen an Tagen mit Abwesenheiten

    Dieser SQL-Ordner zeigt als Resultat eine Liste von offenen und verrechneten Leistungen, welche an Tagen mit Abwesenheiten erfasst wurden.

    • Suchdialog anzeigen: ja
    • Klasse: Leistung
    • Suchfelder: Von (Datum), Bis (Datum)

    Suche in ganztägigen Abwesenheiten:

      bold_id in (select bold_id from offeneleistung ol where
      datum >= '\1' and datum <= '\2' and
      (
      (select count(*) from abwesenheit abw1 where minutenabwesend is null and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
      or
      (select count(*) from abwesenheit abw3 where
      abw3.minutenabwesend is null and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
      abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
      ) > 0
      )
      )
      or
      bold_id in (select bold_id from verrechneteleistung vl where
      datum >= '\1' and datum <= '\2' and
      (
      (select count(*) from abwesenheit abw2 where minutenabwesend is null and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
      or
      (select count(*) from abwesenheit abw4 where
      abw4.minutenabwesend is null and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
      abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
      ) > 0
      )
      )

      Wenn zusätzlich auch in stundenweisen Abwesenheiten gesucht werden soll:

      • Erfordert ein zusätzliches Suchfeld: Auch in stundenweisen Abwesenheiten suchen? (Boolean)
      bold_id in (select bold_id from offeneleistung ol where
      datum >= '\1' and datum <= '\2' and
      (
      (select count(*) from abwesenheit abw1 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
      or
      (select count(*) from abwesenheit abw3 where
      (abw3.minutenabwesend is null or abw3.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
      abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
      ) > 0
      )
      )
      or
      bold_id in (select bold_id from verrechneteleistung vl where
      datum >= '\1' and datum <= '\2' and
      (
      (select count(*) from abwesenheit abw2 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
      or
      (select count(*) from abwesenheit abw4 where
      (abw4.minutenabwesend is null or abw4.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
      abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
      ) > 0
      )
      )

      Projekte (Klasse: Projekt)

      Projekte mit offenen Leistungen bis Datum

      Dieser SQL-Ordner gibt Ihnen alle Projekte aus, die bis zu einem bestimmten Datum noch offene Leistungen haben.

      Suchdialog anzeigen: ja

      Firebird und MS SQL-Server

      bold_id in (select projekt from offeneLeistung where Datum<="\1")

      Achtung: Bereits verrechnete Leistungen werden nicht berücksichtigt, selbst wenn die Leistung zum eingegebenen Datum noch offen war.

      Aktive Projekte ohne Leistungen

      SQL-Ordner, der alle Projekte findet, die seit einem bestimmten Datum weder offene noch verrechnete Leistungen hatten, aber noch aktiv sind. Im Abfragedialog wird das Stichdatum angegeben.

      Suchdialog anzeigen: ja

      Firebird und MS SQL-Server

      aktiv=1 and bold_id not in (select projekt from offeneLeistung where Datum>="\1") and 
      bold_Id not in (select projekt from verrechneteLeistung where Datum >="\1")

      Projekte mit produktiven Projekttypen und offenen Leistungen

      Dieser SQL-Ordner gibt Ihnen alle produktiven Projekte aus, die noch offene Leistungen haben.

      Suchdialog anzeigen: nein

      Firebird und MS SQL-Server

      typ in (select bold_id from projekttyp where produktiv=1) and bold_id in (SELECT projekt from offeneleistung)

      Phasen (Klasse: Projektphase)

      Phasen und Subphasen eines Projekts

      Es sollen alle Phasen und Subphasen eines Projekts angezeigt werden. Im Beispiel wird der Projekt-Code abgefragt.

      Suchdialog anzeigen: ja

      Firebird und MS SQL-Server

      (projekt in (select bold_id from projekt where code like "%\1%")) or 
      (parentphase in (select ph.bold_id from projektphase ph where ph.projekt in
      (select bold_id from projekt where code like "%\1%")))

      Diese Expression funktioniert nur für die erste Ebene von Subphasen. Subphasen von Subphasen werden nicht berücksichtigt.

      Die Schwierigkeit besteht darin, dass Subphasen nur die Phase kennen, an der sie hängen, nicht aber das Projekt. Es muss also sowohl nach dem Projekt wie auch nach dem Projekt der Parentphase gesucht werden.

      Da zwei mal die gleiche Tabelle (Projektphase) für unterschiedliche Anfragen angesprochen wird, muss mit Aliases gearbeitet werden (select ph.bold_id from projektphase ph where...)

      Bearbeiter

      Präsenzzeiten nach Bearbeiter von-bis

      Zeigt die Präsenzzeiten nach Bearbeiter in einem anzugebenden Datumsintervall.

      Als Defaultwerte werden der aktuell eingeloggte Benutzer sowie der letzte Monat angegeben. Im Suchdialog kann diese Abfrage dann auch anders formuliert werden.

      • Klasse: Praesenzzeit (muss von Hand ins Feld eingetippt werden)

      Abfragen definieren...

      In der Abfrage werden drei Suchfelder definiert:

      FeldnameTyp des FeldesDefaultwert
      Kürzel Zeichen Timsession.allinstances->first.login.kuerzel
      Von (Datum) Datum Erster des letzten Monats: date.incMonth(-1).firstofmonth
      Bis (Datum) Datum Letzter des letzten Monats: date.incMonth(-1).lastofmonth

      Firebird und MS SQL-Server

      bold_id in (select bold_id from praesenzzeit where bearbeiter in 
      (select bold_id from projektbearbeiter where UPPER(kuerzel) = UPPER('\1')))
      and datum>='\2' and datum<='\3'
      • Suchdialog anzeigen: ja

      Listeneinstellungen für Spalten

      NameExpressionErweitere Listeneinstellungen
      Bearbeiter bearbeiter
      Datum datum
      Von von.formatdatetime('hh:mm')
      Bis bis.formatdatetime('hh:mm')
      Präsenzzeit minuten dbmTim.MinuteRenderer, Spalte summieren

      Aktivitäten

      Suche nach E-Mails (Aktivitäten mit der Kontaktart E-Mail):

      Klasse: Aktivitaet

      FeldnameTyp des Feldes
      Titel Zeichen
      Text Zeichen
      Absender Zeichen
      Empfänger Zeichen

      SQL-Expression Firebird

      KontaktArt=1 and Titel like '%\1%' and text containing '\2' and EmailSender like '%\3%' and EmailRecipients like '%\4%'

      SQL-Expression MS SQL-Server

      KontaktArt=1 and Titel like '%\1%' and text like '%\2%' and EmailSender like '%\3%' and EmailRecipients like '%\4%'

      Allgemeine Aktivitätensuche

      Ein SQL Suche nach Aktivität nach diversen Kriterien: Titel, Kontakt, Datum von-bis, Dateiname, Projekt (Code, Beschrieb, Betreffend), Aktivitätstyp; welche mit AND verknüpft sind und leere Eingaben zulassen.


      Klasse: Aktivitaet

      FeldnameTyp des Feldes
      Titel Zeichen
      Kontakt Zeichen
      Datum von Datum
      Datum bis Datum
      Datei Zeichen
      Projekt Zeichen
      Aktivitätstyp Zeichen

      SQL-Expression für Firebird

      Titel containing '\1' and Pfad containing '\5' and
      (
      BOLD_ID = (case when CHARACTER_LENGTH('\2') = 0 then bold_id else - BOLD_ID end) or
      adresseintrag in (select BOLD_ID from Adresseintrag where Alias containing '\2')
      )
      and
      (
      BOLD_ID = (case when CHARACTER_LENGTH('\3') = 0 then bold_id else - BOLD_ID end) or
      CAST(Datum as Date) >= CAST('\3' as Date)
      )
      and
      (
      BOLD_ID = (case when CHARACTER_LENGTH('\4') = 0 then bold_id else - BOLD_ID end) or
      CAST(Datum as Date) < CAST('\4' as Date)+1
      )
      and
      (
      BOLD_ID = (case when CHARACTER_LENGTH('\6') = 0 then bold_id else - BOLD_ID end) or
      projekt in (select BOLD_ID from Projekt where Code containing '\6' or Beschrieb containing '\6' or Betreffend containing '\6')
      )
      and
      (
      BOLD_ID = (case when CHARACTER_LENGTH('\7') = 0 then bold_id else - BOLD_ID end) or
      typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung containing '\7')
      )

      SQL-Expression für MS SQL-Server

      Titel like '%\1%' and Pfad like '%\5%' and
      (
      BOLD_ID = (case when len('\2') = 0 then bold_id else - BOLD_ID end) or
      adresseintrag in (select BOLD_ID from Adresseintrag where Alias like '%\2%')
      )
      and
      (
      BOLD_ID = (case when len('\3') = 0 then bold_id else - BOLD_ID end) or
      Datum >= '\3' or Datum is Null
      )
      and
      (
      BOLD_ID = (case when len('\4') = 0 then bold_id else - BOLD_ID end) or
      Datum < cast('\4' as datetime)+1 or Datum is null
      )
      and
      (
      BOLD_ID = (case when len('\6') = 0 then bold_id else - BOLD_ID end) or
      projekt in (select BOLD_ID from Projekt where Code like '%\6%' or Beschrieb like '%\6%' or Betreffend like '%\6%')
      )
      and
      (
      BOLD_ID = (case when len('\7') = 0 then bold_id else - BOLD_ID end) or
      typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung like '%\7%')
      )

      11.07.2006 | 19.10.2016: Korrekturen bei "Datumsfelder mit Zeitteil": Es braucht bei den Firebird-Abfragen keinen Cast. Ausserdem war die Abfrage des (Bis-Datum +1) mit <= angegeben, was falsch ist, es muss < sein.
      Produktlinien: Standard, Expert
      Module: Leistung & CRM