DB Access Module for DB2 CLI User’s Guide : Chapter 2 Technical Information : Expressions
Expressions
Although all DB Access Modules are implemented through the same interface, there are differences in the parameters that must be passed to certain expressions. The following section outlines the differences in the DB Access Module for DB2 CLI.
Global Functions
The DB Interface Module provides a predefined set of global functions, which allow an application to build portable SQL expressions. Table 4 lists restrictions on the global functions associated with RWDBExpr.
Table 4 – Restrictions on the use of global functions with RWDBExpr 
Function
Restrictions
rwdbAvg(const RWDBExpr&)
None. Corresponds to: AVG(expr)
rwdbCast(const RWDBExpr&,
const RWDBValue&)
None. Corresponds to:CAST(expr AS val)
rwdbCast(const RWDBExpr&,
const RWDBValue&,
const RWDBExpr&
Same as the two-expression form, as DB2 does not support this form.
rwdbCast(const RWBDExpr&,
const RWDBValue&,
const RWDBExpr&,
const RWDBExpr&)
Same as the two-expression form, as DB2 does not support this form.
rwdbCharLength(const RWDBExpr&)
None. Corresponds to: {fn LENGTH(expr)}
rwdbCount()
None. Corresponds to: COUNT(*)
rwdbCount(const RWDBExpr&)
None. Corresponds to: COUNT(expr)
rwdbCountDistinct(const RWDBExpr&)
None. Corresponds to: COUNT(DISTINCT expr)
rwdbCurrentUser()
None. Corresponds to: {fn USER()}
rwdbExists(const RWDBSelectorBase&)
None. Corresponds to: EXISTS
rwdbLower(const RWDBExpr&)
None. Corresponds to: {fn LCASE(expr)}
rwdbMax(const RWDBExpr&)
None. Corresponds to: MAX(expr)
rwdbMin(const RWDBExpr&)
None. Corresponds to: MIN(expr)
rwdbName(const RWCString&,
const RWDBExpr&)
None. Corresponds to: expr AS str
rwdbPosition(const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn LOCATE(expr0, expr1)}
rwdbSessionUser()
None. Corresponds to: {fn USER()}
rwdbSubString(const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn SUBSTRING(expr0, expr1, { fn LENGTH(expr0) }) }
rwdbSubString(const RWDBExpr&,
const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn SUBSTRING (expr0, expr1, expr2) }
rwdbSum(const RWDBExpr&)
None. Corresponds to: SUM(expr)
rwdbSystemDateTime()
None. Corresponds to: { fn NOW() }
rwdbSystemUser()
None. Corresponds to: {fn USER()}
rwdbTrimLeading(const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn LTRIM(expr1)}
rwdbTrimTrailing(const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn RTRIM(expr1)}
rwdbTrimBoth(const RWDBExpr&,
const RWDBExpr&)
None. Corresponds to:{fn LTRIM(RTRIM(expr1)) }
rwdbUpper(const RWDBExpr&)
None. Corresponds to: {fn UCASE(expr)}
Outer Joins
DB2 supports ANSI SQL 92 syntax, so outer joins for DB2 should be written in the FROM clause. 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.
The next example shows how you would write an outer join program for DB2. You can assume that myDbase is a valid RWDBDatabase instance.
An Outer Join for DB2 in 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 join1 and the table loc, forming a nested join.
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate);
 
// Attach the join criterion to 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);