Join-Queries mit temporären Tabellen (und es funktioniert eben doch)

Join-Queries mit temporären Tabellen (und es funktioniert eben doch)

Dieser Beitrag bezieht sich auf den englischsprachigen Artikel Queries with temporary tables (nevertheless it works)

Die Verwendung von temporären Tabellen innerhalb Queries kann ab und an zu Problemen führen; im Speziellen, wenn man die Fehlermeldung "Ein Datensatz in % (%) kann nicht ausgewählt werden. Temporäre Tabellen müssen bei Verknüpfung mit persistenten Tabellen äußere Tabellen sein." erhält. Ein Arbeitskollege hat allerdings eine solche Query zu meinem Erstaunen trotzdem zum Laufen gebracht. Auf der Grundlage seiner Vorarbeit habe ich das Thema mal genauer untersucht.

Die Inhaltsgrundlage dieses Beitrages basiert auf dem Wissen durchgeführten Tests. Die Funktionsweise von Queries mit temporären Tabellen wird hier allein durch Beobachtungen erklärt (auch wenn AX vielleicht anders als angenommen arbeitet).

Der genannte Fehler wird ausgelöst wenn die Datenselektion der jeweiligen Datenquelle nicht auf dem gleichen Tier ausgeführt wird, wie die übergeordnete (parent) Datenquelle. Die erste Datenquelle nimmt allerdings eine Sonderstellung ein. Die Datenquellen werden nämlich auf dem Tier abgearbeitet wo sich der Datenpuffer der Datenquelle befindet. Hat aber der Server-Tier einmal mit der Abarbeitung einer Datenquelle begonnen, kann für die weitere Datenselektion nicht mehr zurück auf den Client-Tier gewechselt werden.

Unter Beachtung einiger Regeln steht für eine Join-Query mit temporären Tabellen aber absolut nichts im Wege. Es gilt zu beachten:

• Persistente Tabellen werden stets auf dem Server-Tier verarbeitet
• Temporäre Tabellen werden auf dem Tier verarbeitet, auf den das Handle der Tabelle hinzeigt
• Temporäre Tabellen, die üblicherweise persistent sind, aber mit der setTmp()-Methode zur Laufzeit temporär gestellt werden, werden auf dem Tier gehalten, wo die setTmp()-Methode angewendet wird
  
  CustTable custTable
  ;
  custTable.setTmp(); // now the handle will fixed on the current tier
  
  
• Temporäre Tabellen welche bereits auch als solche im AOT definiert sind, werden dort gehalten wo sich auch die Deklaration befindet
  
  TmpSysQuery tmpSysQuery; // the handle is already fixed on the current tier
  ;
  
  
• Die erste Datenquelle in der Query kann ihren Datenpuffer wahlweise vom Client- oder Server-Tier beziehen
• Alle weiteren, und somit eingebetteten Datenquellen müssen ihren Datenpuffer entweder auf dem gleichen Tier wie ihre übergeordnete (parent) Datenquelle oder aber auf dem Server-Tier halten

Folgende Konstellationen sind demnach möglich oder eben nicht (einige Beispiele):
(Siehe englischer Artikel)

Fazit: Die Meldung "Ein Datensatz in % (%) kann nicht ausgewählt werden. Temporäre Tabellen müssen bei Verknüpfung mit persistenten Tabellen äußere Tabellen sein." würde wohl besser heissen: "Temporäre Datenquelle % bezieht Daten nicht aus dem gleichen Tier wie die übergeordnete Datenquelle % noch vom Server-Tier selbst.".

Hier noch ein paar allgemeine Anmerkungen zur Verwendung der setTmp()-Methode für persistente Tabellen:

• Die setTmp()-Methode muss immer in derselben Methode wie die Deklaration der Tabelle erfolgen
• Es darf noch keine Manipulation mit dem Tabellenpuffer erfolgt sein (andernfalls hat die setTmp()-Methode keinen Einfluss mehr auf den Puffer!)
• Für Datenmanipulationen sollten die Skip-Methoden wie skipDataMethods()/skipDeleteMethods()/skipEvents()/skipDataBaseLog() verwendet werden und mit den Befehlen doUpdate(), doInsert() und doDelete() gearbeitet werden um ungewollte Änderungen an der persistenten Tabelle in der Datenbank zu verhindern

(Inhalt bezieht sich auf das QueryRun Objekt der Versionen AX 3.0 3tier, AX 4.0 und AX 5.0)

Join-Queries with temporary tables (nevertheless, it works)

Usage of temporary tables often ends in trouble, especially when the error message “Cannot select a record in % (%). Temporary tables must be the inner tables when joined to permanent tables.“ rises up.

But a workmate of mine has solved the issue of that message with a special client/server constellation. Basing his solution I did some further research.

This article is based on knowledge elaborated by tests. All explanation of query functionality is related to its results (even AX perhaps does not work as described).

All problems begin, when the data selection of a data source is made in a different tier than its parent data source. Only the first data source has free tier choice. Data sources will be fetched on the tier where its data buffer is located. Once the server tier has become as data handler, it is not possible to re-access client data.

But if you take care to some rules, a query with temporary tables will run fine. Keep in mind, that:

• Permanent tables are fetched on server tier
• Temporary tables are fetched on there, where its data buffer is located:
• Temporary tables, which are usually permanent, but made temporary with a setTmp() call, gets its tier where you make the setTmp()-call.
  
  CustTable custTable
  ;
  custTable.setTmp(); // now the handle will fixed on the current tier
  
  
• Temporary tables, which are already declared in AOT as temporary, gets its tier there, where its declaration is located
  
  TmpSysQuery tmpSysQuery; // the handle is already fixed on the current tier
  ;
  
  
• First data source’s data buffer can either be located on the client tier or on the server tier as well
• Every other, and therefore embedded data source must have its data buffer on the same tier as the parent data source or on the server tier

Look these examples which are possible or even not (not all possibilities listed …):

Summary: The message “Cannot select a record in % (%). Temporary tables must be the inner tables when joined to permanent tables.“ would better be named as “The temporary table's data buffer % is neither on the same tier as its parent % nor on the server tier”.

Some common remarks when using permanent tables as temporary tables:

• The setTmp() method should done in the same method as where the table’s declaration is located
• Between declaration and setTmp()-call no buffer manipulation is allowed (otherwise setTmp() will not work!)
• When doing data manipulations use the skip-methods like skipDataMethods()/skipDeleteMethods()/skipEvents()/skipDatabaseLog() and the commands doUpdate(), doInsert() and doDelete() to prevent unwanted manipulation on the permanent data base table

(Content is related to the QueryRun Object from Version AX 3.0 3tier, AX 4.0 und AX 5.0)