...

xsql fügt Excel 3 Funktionen in einer neuen Kategorie [xsql] hinzu:

• fQuery - fColumns -Start einer SQL-Abfrage beim Öffnen der Datei oder bei Änderungen an den relevanten Zellen
• fTables -

...

• Liste von Tabellen (physischen Dateien) in Bibliotheken erstellen
• fColumns - Liste von Felder in Dateien (physische und logische) erstellen

Das Arbeiten mit diesen Funktionen wird in diesem Kapitel beschrieben.

...

Eine xsql-Funktion mithilfe des Excel-Formeleditors einfügen

Die Tabellenfunktion fQuery kann wie jede andere Funktion direkt in die Zelle oder über den Formeleditor eingegeben werden:

...

1. Registerkarte "Formeln" anklicken
2. "Funktion einfügen" klicken
3. Im Dropdownfeld "Kategorie" [XSQL] auswählen
4. "fQuery" markieren und OK klicken

Image Removed

...

1. die gewünschte Funktion markieren und OK klicken

Image Added

Die xsql-Funktionen sind Array-Funktionen

Hier eine kleine Hilfestellung, falls Sie nicht wissen, wie man Array-Funktionen erstellt:

Am einfachsten:

1. Geben Sie die Funktion einer Zelle ein (hier zum Beispiel fQuery):

Image Added

2. Drücken Sie ENTER

Image Added

Beachten Sie die Zelle, in der Sie die Funktion eingegeben haben.

- wenn sie jetzt die Überschrift des ersten ausgewählten Feldes hat, dann haben Sie die Funktion syntaktisch korrekt eingegeben. Fahren Sie mit Schritt 3 fort.
- wenn sie jetzt den Inhalt ##WERT hat, dann gibt es noch einen Fehler in der eingegebenen Funktion. Bitte korrigieren Sie die Funktion.

3. Markieren Sie soviele Spalten, wie Felder ausgewählt wurden und mindestens soviele Zeilen, wie Sie an Sätzen erwarten (mehr ist nicht schlimm)

Image Added

4. Drücken Sie die Taste F2

Image Added

5. Drücken Sie die Tastenkombination STRG+UMSCH+EINGABE

Image Added

Ein vorhandenes Array erweitern

Zum Vergrössern eines Arrays (gilt für alle Arrayformeln, d. h. auch Doku zu Excel beachten):

• Zellzeiger in die linke obere Ecke der Arrayformel setzen
• Den gewünschten (vergrösserten) Bereich markieren
• F2 drücken
• STRG+UMSCH+EINGABE drücken

Bitte beachten: der neue Bereich muss größer oder gleich groß wie der alte sein, dies betrifft Zeilen und Spalten! Ist dies nicht der Fall (z. B. mehr Zeilen aber weniger Spalten) erscheint die Fehlermeldung "Teile eines Arrays können nicht geändert werden".

Zum Verkleinern von Arrayformeln geht diese Methode leider nicht.

Siehe auch:

Microsoft Excel: Regeln für ein Ändern von Matrixformeln

Die Funktion fQuery

fQuery führt eine SQL-Abfrage aus und liefert das Ergebnis in die Tabelle.

Syntax von fQuery

fQuery(SQL; Optionen; DNS; P1; P2; P3; ...)

Die einzelnen Parameter sind:

SQL: Die SQL Anweisung

Optionen (wahlweise)für die Überschrift:

0 - keine Überschriften
1 - Feldnamen als Spaltenüberschriften ausgeben
2 - Feldtexte als Spaltenüberschriften ausgeben
4 Kurztexte ("COLHDG") 
4 - Langtexte ("TEXT")
8 - Unbenutzte Zellen als #N/V ausgeben8 - Statt des Abfrageergebnisses wird die Beschreibung der Felder ausgegeben

Die Optionen können addiert werden.

DSN: Name der ODBC Datenquelle.

Falls leer, sucht fQuery unter den vorhandenen DSN's die erste iSeries-Verbindung.

Wenn man eine Datenquelle angeben möchte, dann ist sie in Anführungszeichen (") zu setzen.

P1, P2, P3, ... (wahlweise): Zellbezüge für Platzhalter (Parametermarker im Statement): Fragezeichen im SQL StatementBeispiel

Beispiele für fQuery:

...

=fQuery("SELECT * FROM QIWS/QCUSTCDT",1)

Voraussetzung ist das Wissen über die Eingabe/Verwendung einer Arrayformel.

Am einfachsten:

Erst Formel in eine Zelle eingeben und EINAGBE drücken. Man sieht dann gleich ein Ergebnis und weiß, ob die Syntax richtig war. Dann den gewünschten Bereich markieren, F2 drücken, dann STRG+UMSCH+EINGABE

Natürlich kann die Formel nur soviele Zeilen/SPalten zurückgeben, wie der markierte Bereich groß ist, man kann jedoch ruhig mit ein paar Tausend Zeilen etc. operieren.

Die Funktion fColumns

;1)

• holt alle Felder von allen Sätzen der Datei QIWS/QCUSTCDT und schreibt sie in die Tabelle
• es wird die erste IBM i - ODBC-Datenquelle verwendet
• als Überschriften werden die Feldnamen verwendet

=fQuery("select * from qiws/qcustcdt where cdtlmt >= ?";1;"AETOAD";$C$1)

• holt alle Felder von ausgewählten Sätzen der Datei QIWS/QCUSTCDT und schreibt sie in die Tabelle
• Es wird die Datenquelle AETOAD verwendet
• als Überschriften werden die Feldnamen verwendet
• die Auswahl wird durch den Inhalt der Zelle C1 bestimmt: alle Sätze, in denen das Feld CDTLMT grösser oder gleich dem Inhalt von C1 ist, werden geholt

Image Added

Die Funktion fTables

Die Funktion liefert die Namen und Beschreibungen von Tabellen (physischen Dateien) in einer Bibliothek

Syntax von fTables

fTables(bibliothek; tabelle; spaltenüberschrift)

Die Datei liefert die Namen und Beschreibungen von Tables (physischen Tabellen) gemäss den angegebenen Auswahlkriterien in Parameter 1 (biblitohek) und Parameter 2 (tabelle).

Die Parameter sind in Grossbuchstaben einzugeben:

bibliothek: Der Name einer Bibliothek/eine Schemas

Hier können Sie auch das Prozentzeichen (%) als Wildcard verwenden.

• % liefert alle Einträge aus der Bibliotheksliste des ODBC-Verarbeitungsjobs
• Gültige Sonderwerte: *ALL, *USRLIBL, *LIBL

tabelle: der Name der aufzulistenden physischen Datei (Table) oder Dateien

Hier können Sie auch das Prozentzeichen (%) als Wildcard verwenden

Beispiele:

ST% - findet alle Dateien, der Name mit "ST" beginnt
%KFZ% - findet alle Dateien mit "KFZ" im Namen, egal, was davor oder dahinter steht
%P - findet alle Dateien, dern Name mit "P" endet
ST%P - findet alle Dateien, deren Name mit "ST" beginnt und mit "P" endet

spaltenüberschrift: Überschriften, oder nicht

Gültige Werte:

0 - keine Überschriften
1 - Feldnamen
8 - Unbenutzte Zellen als #N/V ausgeben

Beispiele für fTables

Auswahlkriterien in den Zellen A1, A2 und A3:

Image Added

Alle Dateien:

Image Added

Alle, die mit "OA" beginnen:

Image Added

Alle, in deren Name "MS" vorkommt:

Image Added

Die Funktion fColumns

Die Funktion liefert Informationen über Felder von Dateien (physische und logische) zurück: Name, Typ, Dimensionen, Pufferposition, u.a.

Syntax von fColumns

fColumns(bibliothek; tabelle; feldname ; spaltenüberschrift)

Die Parameter sind in Grossbuchstaben einzugeben:

bibliothek: Der Name einer Bibliothek/eine Schemas

Hier können Sie auch das Prozentzeichen (%) als Wildcard verwenden.

• % liefert alle Einträge aus der Bibliotheksliste des ODBC-Verarbeitungsjobs
• Gültige Sonderwerte: *ALL, *USRLIBL, *LIBL

tabelle: der Name einer Datei in der Bibliothek

Hier können Sie auch das Prozentzeichen (%) als Wildcard verwenden:

Beispiele:

ST% - findet alle Dateien, der Name mit "ST" beginnt
%KFZ% - findet alle Dateien mit "KFZ" im Namen, egal, was davor oder dahinter steht
%P - findet alle Dateien, dern Name mit "P" endet
ST%P - findet alle Dateien, deren Name mit "ST" beginnt und mit "P" endet

feldname: der Name der aufzulistenden physischen Datei (Table) oder Dateien

Hier können Sie auch das Prozentzeichen (%) als Wildcard verwenden:

Beispiele:

ST% - findet alle Felder, der Name mit "ST" beginnt
%KFZ% - findet alle Felder mit "KFZ" im Namen, egal, was davor oder dahinter steht
%P - findet alle Felder, dern Name mit "P" endet
ST%P - findet alle Felder, deren Name mit "ST" beginnt und mit "P" endet

spaltenüberschrift: Überschriften, oder nicht

Gültige Werte:

0 - keine Überschriften
1 - Feldnamen
8 - Unbenutzte Zellen als #N/V ausgeben

Beispiele für fColumns

Auswahlkriterien in den Zellen A1, A2, A3 und A4:

Image Added

Alle Felder einer bestimmten Datei

Image Added

Alle Felder aller Dateien in der Bibliothek
(hier sollte man aufpassen, dass man die Matrix gross genug aufzieht...)

Image Added

Alle Felder aller Dateien, die "KDN" in ihrem Namen tragen

Image Added

Alle Felder in Dateien, die auf "P" enden und deren Name "KMP" enthält

Image Added