SQL-Statement (Access = TRANSFORM)

[Andre_P]

Hallo,

ich habe folgende Frage:

Unter Access kann man Kreuztabellenabfragen erstellen, die ganz hilfreich sind. Ich benötige eine solche Abfrage auf der AS/400, und die ich mit STRSQL, RUNSQLSTM oder wie auch immer ausführe.

Die Tabelle sieht in diesem Beispiel so aus:

ID datum sorte tonnage
1 2002-10-10 305 34233
2 2002-11-20 103 450
3 2002-05-20 103 550
4 2002-11-20 305 3200

Access erzeugt dabei folgenden SQL-Statement:

TRANSFORM Sum(tonnagen.tonnage) AS [Summe von tonnage]
SELECT tonnagen.sorte
FROM tonnagen
GROUP BY tonnagen.sorte
PIVOT Year([Datum]) & "-" & Format(Month([Datum]),"00");


Als Abfrageergebnis wird folgendes geliefert:

sorte 2002-05 2002-10 2002-11
103 550 450
305 34233 3200


Wenn ich diesen SQL-Statement auf der AS/400 ausführe, erkennt er die Anweisung TRANSFORM nicht an. Ist dieser Ausdruck Access-Spezifisch? Gibt es unter SQL einen vergleichbaren Ausdruck?
Oder jetzt werd ich frech: Wie würde eine SQL-Anweisung für so ein Abfrageergebnis unter SQL auf der AS/400 lauten?

Vielen Dank schon mal im voraus.

Viele Grüße
André

[Fuerchau]

TRANSFORM und PIVOT sind ausschließlich MS/Access-spezifisch und entsprechen nicht der ANSI-SQL !
Der Select könnte auf der AS/400 folgendermassen aussehen:

select substr(datum, 1, 7) as YYMM, sorte, sum(tonnage) as tonnage
from tonnagen
group by YYMM, sorte
order by YYMM, sorte

Das Ergebnis sind dann einzelne Sätze:
YYMM sorte tonnage
2002-05 103 550
2002-10 305 34233
2002-11 103 450
2002-11 305 3200

[Andre_P]

Vielen Dank für die Antwort!

Schade, dass meine Befürchtung sich doch bestätigt hat, bezüglich des TRANSFORM und PIVOT... Wär ja auch zu schön gewesen.

Daher mal paar Fragen an die Profis:
Ich kenn mich ganz gut mit MS Access aus.
Unsere Warenwirtschaft auf der AS/400 liefert nicht ausreichende Auswertungen (wie z.B. diese einfache Access-Abfrage). Es gibt zwar die Möglichkeit, über eine Funktion die Daten in eine CSV-Datei auszugeben und diese später in Excel zu bearbeiten, ist aber nicht so perfekt...
Daher war mein Gedanke, mittels eines SQL-Servers und MS Access 2000 entsprechende Auswertungen flexibler im Access zu erstellen. Mit Access kann man den Reports ein ganz anderes Aussehen verpassen. Kennt sich jemand damit aus? Was ist bei einer ODBC-Anbindung des SQL-Servers grundsätzlich zu beachten? Ab wie vielen Datensätzen stößt das ganze auf die Grenzen? Ist solche Verarbeitung (Zugriff von SQL-Server) für die AS/400 belastend?
Würde man eine Jahresauswertung erstellen, die auf Lieferscheinen basiert, wären es so ca. 45.000 bis 50.000 Lieferscheine im Jahr, die ausgewertet werden müssten. Also z.B. Kundenumsatzstatistiken, Artikelumsatzstatistiken usw. Die werden dann natürlich zu Summen pro Kunde/Artikel oder nur Artikel gruppiert, also logischerweise nicht eine unendliche Liste mit einzelnen Lieferscheinen. Da die Anwendung mandantenfähig ist, liegen die Daten aller Mandanten und aller Jahre (da die Anwendung seit ca. 2 Jahren läuft, noch nicht so viele Datensätze) in einer Tabelle und werden durch die eindeutige Mandantennummer gekennzeichnet. Das Beispiel mit 50.000 LS ist schon der extremste Mandant. Sonst sind es, schätze ich mal, so max. 20.000 LS pro Mandant im Jahr. Würde bei 13 Mandanten x 20.000 LS + 2 Mandanten x 50.000 LS im Jahr ein Datenaufkommen von ca. 360.000 Datensätzen bedeuten. Das zu Tabellengröße. Es werden aber keine mandantenübergreifende Auswertungen erstellt, sondern immer für einen Mandanten und meistens Monats-, Quartals- und Jahresberichte. Seltener aber trotzdem Berichte basierend auf Kreuztabellenabfragen, die darstellen, wieviel von jedem Artikel (Zeile) (ca. 100 Artikel) in letzten 2-5 Jahren (Spalte) verkauft wurden. Die Frage zur Performenz steht weiter unten :-)

Ich könnte mir vorstellen, dass für die Verkaufsabteilung ein sehr hilfsreiches Werkzeug auch die Formularanzeige und -erfassung wäre.
Nehmen wir mal an, ich erstelle ein Formular mit folgenden Unterformularen:

1. Kunden (selektierbar)
2. Angebote (Kopfdaten)
3. Angebotsdetails
4. Aufträge (Kopfdaten)
5. Auftragsdetails

Sollte folgendermaßen funktionieren:
Das Formular wird geöffnet. Danach wird ein Suchbegriff für einen Kunden eingegeben. Im 1. Unterformular werden die Kunden zu dem Suchbegriff angezeigt. Danach nach einem Klick auf einen bestimmten Kunden sollen zu dem Kunden jeweils die Angebots- und Auftragsdaten angezeigt werden (anhand der Kundennummer, nicht alle, sondern für einen bestimmten Zeitraum, der als Standardwerte in entsprechenden Feldern vorgeschlagen wird und entpsrechend gefiltert werden soll). Nach einem Klick auf einen Auftrag oder Angebot sollen die Details angezeigt werden, die wiederrum aus jeweils anderen Tabelle kommen und anhand der eindeutigen Nummer gefiltert werden. Das heißt, die Unterformulare sind jeweils auf Abfragen aufgebaut. Von der Programmierung her ist das kein Problem.

Meine Frage ist, wie performant würde das ganze funktionieren? Wie ist denn das, holt sich der SQL-Server von der AS/400 nur die Datensätze, die den Kriterien entsprechen oder cacht er erstmal den ganzen Datenbestand und fragt erst dann die Daten ab und liefert dann das Ergebnis? (ich hoffe, es ist das 1.)

Vielen Dank schon mal für Antworten und Ideen im voraus.

Gruß
André

[Fuerchau]

Hallo Andrè, deine Ausführungen sind ja doch sehr langatmig.
Suche doch mal zum Thema Access in dieser Datenbank.

Meine Erfahrungen mit ODBC sind sehr gut.
Die meiste Arbeit erledigt die AS/400 und liefert tatsächlich nur die benötigten Daten.
Ob eine Abfrage performant ist, testest du am besten mittels SQL auf der AS/400, den SQL-Befehl kann man sich in Access ja ganz gut ansehen.

Vorsicht ist natürlich mit Cross-Joins, Transform und Pivot gegeben, da ggf. wirklich alle Sätze zum Client übertragen und ggf. wieder verworfen werden.
Das gleiche gilt für Verknüpfungen unterschiedlicher Datenherkunft (lokale Tabelle und AS/400-Tabelle) sowie für Abfragen auf Abfragen.

ODBC über den SQL-Server würde ich nicht empfehlen, da durch die doppelte Umsetzung Performanceverluste möglich sind.
Direkter ODBC ist erheblich schneller.
Damit nicht bei jedem User die ODBC-Verbindung manuell erstellt werden muss, kann man in einem Access-Modul auch DAO.RegisterDatabase verwenden.

Solange der Benutzer an den Abfragen nicht rumbasteln kann, können alle Abfragen auf ihre Performance vorher getestet werden.
Sollten Zugriffspfade fehlen, können diese jederzeit (ohne zu Lasten der bestehenden Anwendung) erstellt werden.

[minimax]

Warum denn die Angst vor dem bösen ODBC?

Ein SQL-Server ist doch eine schöne mächtige und performante Datenbank. Ich habe da bisher keine Probleme damit gehabt ein paar Mio datensätze reinzupumpen und darauf kreuz und quer Abfragen zu erstellen.

Warum also holt Ihr nicht einfach in einem geschedulden Job (via SQL-Server-Agent) über eine ganz normale client-Access-ODBC-Connection beliebig viele Tabellen aus der AS400 herüber in eine Zwischendtabell (select * from Tabelle) evtl. nur Delta (...wher Datum>getdate()-5) o.ä.
Selektiert dann aus dieser Tabelle neue Datensätze heraus

z.B.
select * from Tabelle where ID not in (select ID from Tabelle2)

und macht dann SQL-Abfragen auf die "Endtabellen"? Die "Last" der Abfragen ist dann vom operativen System weg.

vielleicht noch mit einem schönen bunten Frontend oben drauf ;-)

Gruss
minimax