Expressions
Although all DB Access Modules are implemented through a common 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 Oracle OCI.
Global Functions
Table 4 lists restrictions on the global functions associated with
RWDBExpr.
Table 4 – Restrictions on the use of global functions
Function | Restrictions |
rwdbAvg(const RWDBExpr&) | None. Corresponds to: AVG(expr) |
rwdbCast(const RWDBExpr&, const RWDBValue&) | |
rwdbCast(const RWDBExpr&, const RWDBValue&, const RWDBExpr&) | |
rwdbCast(const RWBDExpr&, const RWDBValue&, const RWDBExpr&, const RWDBExpr&) | |
rwdbCharLength(const RWDBExpr&) | None. Corresponds to: 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: SYS_CONTEXT('USERENV','CURRENT_USER') |
rwdbExists(const RWDBSelectorBase&) | None. Corresponds to: EXISTS(sel) |
rwdbLower(const RWDBExpr&) | None. Corresponds to: LOWER(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 string |
rwdbPosition(const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: INSTR(expr1, expr0) |
rwdbSessionUser() | None. Corresponds to: SYS_CONTEXT('USERENV','SESSION_USER') |
rwdbSubString(const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: SUBSTR(expr0, expr1) |
rwdbSubString(const RWDBExpr&, const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: SUBSTR(expr0, expr1, expr2) |
rwdbSum(const RWDBExpr&) | None. Corresponds to: SUM(expr) |
rwdbSystemDateTime() | None. Corresponds to: SYSDATE |
rwdbSystemUser() | None. Corresponds to: SYS_CONTEXT('USERENV','OS_USER') |
rwdbTrimLeading(const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: LTRIM(expr1, expr0) |
rwdbTrimTrailing(const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: RTRIM(expr1, expr0) |
rwdbTrimBoth(const RWDBExpr&, const RWDBExpr&) | None. Corresponds to: LTRIM(RTRIM(expr1, expr0), expr0) |
rwdbUpper(const RWDBExpr&) | None. Corresponds to: UPPER(expr) |
rwdbCast()
The predefined expression, rwdbCast(), allows an application to change the type of a value in the database to another type. For example, with rwdbCast() you can change a number to a character string. The parameters for rwdbCast() should be specified in the following ways:
• Casting to a character string: rwdbCast(expression,"CHAR")
• Casting to a number: rwdbCast(expression,"NUMBER")
• Casting to a date: rwdbCast(expression,"DATE")
The above calls will produce Oracle conversion functions TO_CHAR, TO_NUMBER and TO_DATE respectively. As shown in Table 4, there are also three-parameter and four-parameter versions of the rwdbCast() expression. These can also be used to provide optional format and language parameters for the conversion functions.
Outer Joins
Oracle supports both of the following:
• the ANSI-compliant syntax for outer joins with the join condition in the FROM clause (recommended)
• the Oracle-specific syntax using the Oracle join operator (+) in the WHERE clause (ANSI-noncompliant)
Oracle recommends using the former option, the
FROM clause
OUTER JOIN syntax rather than the latter Oracle join operator. 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 following example shows how you would write an outer join for Oracle. You may assume that
myDbase is a valid
RWDBDatabase instance.
An Outer Join for Oracle 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 the 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 table loc, forming a nested join.
RWDBJoinExpr join2 = rwdbLeftOuter(join1, locate);
// 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);