Outer Joins
For the DB Access Module for ODBC, the DB Interface Module supports the writing of outer joins using ANSI SQL 92 syntax only. See the examples on constructing outer joins in the section “Outer Join Constructs In the FROM Clause (ANSI-Compliant)” in the DB Interface Module User’s Guide.
Because of a restriction in ODBC syntax, you may
not specify a nested join as a first parameter in a global function. For example, specifying a nested join as a first parameter in
rwdbLeftOuterJoin() when instantiating an
RWDBJoinExpr object results in an error.
The following example shows how you would write an outer join program for ODBC. You can assume that
myDbase is a valid instance of
RWDBDatabase.
NOTE: The DB Access Module for ODBC generates SQL using the ODBC outer join escape sequence. It encloses the join using the syntax: {oj ... }, where ... represents the join expression. If you read the generated SQL, you should know that this is simply a normal part of the code.
An Outer Join for ODBC Using ANSI-Compliant Syntax
RWDBTable employee = myDbase.table("emp");
RWDBTable depart = myDbase.table("dept");
RWDBTable locate = myDbase.table("loc");
RWDBSelector selector = myDbase.selector();
selector << employee["empnum"] << employee["ename"]
<< employee["deptno"] << depart["deptno"]
<< depart["dname"] << depart["locno"]
<< locate["locno"] << locate["lname"];
// Define and declare join1 as a right outer join
// between the tables emp and dept.
RWDBJoinExpr join1 = rwdbRightOuter(employee, depart);
// Attach the join criterion to join1 using
// the on() method. This criterion joins the two tables
// emp and dept by their deptno columns.
join1.on(employee["deptno"] == depart["deptno"]);
// Define and declare join2 as a left outer join
// between the table loc and join1, forming a nested join.
// Note that the nested join must be specified as second
// parameter and not first.
RWDBJoinExpr join2 = rwdbLeftOuter(locate, join1);
// Attach the join criterion to the join2 using
// the on() method. This criterion joins the two tables
// dept and loc by their locno columns.
join2.on(depart["locno"] == locate["locno"]);
// Attach join2 explicitly to the selector FROM clause
// using the from() method.
selector.from(join2);
