Two Table Outer Join With Multiple Conditions

SELECT * FROM CUSTOMER t1

LEFT OUTER JOIN ORDERS t2

ON t1.custno = t2.custno

AND t1.custname = t2.custname

For this query, the IP execute will be called to execute the query on the customer table. The following shows how the IP can determine the conditions and build the results:

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). In this case JOIN_QUERY_SIZE will be 2 and QUERY_PROCESS_ORDER will be 0.

2. The IP will call dam_describeTableByProcessOrder() to get details of each table in the query.

   • dam_describeTableByProcessOrder (iTableProcess Order=0) will return CUSTOMER table information and the TableNum of the CUSTOMER table will be returned as 0.
   • dam_describeTableByProcessOrder(iTableProcess Order=1) will return ORDERS table information and the TableNum of the ORDERS table will be returned as 1.

3. The IP can begin to get the search condition (filter condition) on each of the tables.

   • dam_getTableSearchExp(iTableNum=0) returns:

     SearchExpression: NULL

     Join Expression: NULL

     JoinType: N/A

   • dam_getTableSearchExp(iTableNum=1) returns:

     SearchExpression: NULL

     JoinType: SQL_JOIN_OUTER

     Join Expression: orders.custno = customer.custno AND

     orders.custname = customer.custname

4. The IP can then build results for the CUSTOMER table and a pushdown join rowset for the corresponding ORDERS table. If no matching orders are found, the IP should add an empty rowset for the ORDERS table.

5. If the IP only builds results for ORDERS table, the IP EXECUTE function is called for matching CUSTOMERS records. In this case, to get the column values for each of the join columns in the Join Expression, the IP can call dam_getJoinColValue(iTableNum, iColumnNum, int *piXoType, void *pColData, int *piColDataLen)