The IP can get complete search expression (filter conditions) and join conditions for each table. Any IP that needs to get a complete search expression should use this approach to reduce the result set it retrieves from the back-end. The IP can continue to use pushdown joins to build the matching rowset for inner tables.
The IP should disable index processing, so that the OpenAccess SDK SQL engine does not split the query into subqueries, by calling dam_SetOption (DAM_CONN_OPTION_INDEX_OPTIMIZATION).
When the IP is called to execute a query on a table, it should do the following:
1. Check the Process Order of the table being processed using dam_getInfo(DAM_INFO_QUERY_PROCESS_ORDER), and get the join size using dam_getInfo(DAM_INFO_JOIN_QUERY_SIZE). When the query is a single table query, the JOIN_QUERY_SIZE is 1. Otherwise it is the number of tables in the Join.
2. The IP will call dam_describeTableByProcessOrder() to get details of each table in the query. The dam_describeTableByProcessOrder() will return the Table name and the Table number that uniquely identifies the table in the query.
Note: The Table Process Order is the order number in which the table will be processed. Table Number is the unique number assigned to each table in the query before the SQL engine decides the order in which to process the tables. The SQL pass-through mode of the OpenAccess SDK SQL engine API identifies a table by its TableNumber and column by its TableNumber and SchemaNum.
3. The IP can begin to get the search expression (filter condition) and join expression on each of the tables by using:
int dam_getTableSearchExp(
DAM_HSTMT hstmt, /* input */
Int iTableNum, /* input */
DAM_HLOGEXP *phSearchExp
/* output. Contains filter on the table. */
int *piJoinType, /* join type */
DAM_HLOGEXP *phJoinExp,
/* Contains join conditionson the table.*/
int *pbPartial
);
To get complete data about the Search Expression and Join Expression, the IP should use the SQL pass-through mode of the OpenAccess SDK SQL engine Parse Tree API (damex_describeLogicExp(), damex_describeCond())
4. Once the IP receives the data about the filter conditions and join conditions, it can start to build results from the table that is currently being processed. This table is referred to as the outer table. When building results from the outer table, it can also build a pushdown join rowset of the next inner table. The IP can continue to build a pushdown join rowset, for each of the next inner tables, and associate it with the corresponding outer table. At this moment, the IP can also check for the join type between any inner table and the corresponding outer table. If the join type is not INNER JOIN, it may for any reason (for example, semantic reasons) stop adding the join rowset of the inner table to an outer table row. You can use damex_describeTableJoinInfo to get the join type information.
5. If the IP chooses to only process the first outer table being queried, then the OpenAccess SDK SQL engine will call IP EXECUTE for the inner tables, and the IP can continue using Steps 1 through 4 to build the results. When the IP executes inner tables, it should get the column values for the outer table row being processed by calling dam_getJoinColVal(), so that it can substitute the JoinExp value for the Join Column value and prepare a filter for the inner table.
