Two Table Equijoin

SELECT * FROM emp, dept

WHERE emp.deptno = dept.deptno

AND(emp.name = 'Sue' AND emp.deptno = 55) OR emp.ename = 'Mark'

For the above query, the IP execute is called to execute a query on Table EMP. 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 (iTableProcessOrder=0) will return EMP table information and the TableNum of EMP table will be returned as 0.
   • dam_describeTableByProcessOrder(iTableProcess Order=1) will return DEPT table information and the TableNum of DEPT 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: (emp.name = 'Sue' AND emp.deptno = 55) OR emp.ename = 'Mark'

     Join Expression: NULL

     JoinType: N/A

   • dam_getTableSearchExp(iTableNum=1) returns:

     SearchExpression: NULL

     JoinType: SQL_JOIN_OLD_STYLE implying INNER JOIN

     Join Expression: dept.deptno = emp.deptno

4. The IP can then build results for EMP table and pushdown join rowset for corresponding DEPT table.

5. If it only builds results for EMP table, the EXECUTE function of the IP is called for matching DEPT 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(hstmt, iTableNum, iColumnNum, int *piXoType, void *pColData, int *piColDataLen)