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)