Expressions

DB Access Module for Oracle OCI User's Guide
Rogue Wave web site: Home Page | Main Documentation Page

2.4 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.

2.4.1 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&)`	See Section 2.4.2.
`rwdbCast(const RWDBExpr&, const RWDBValue&, const RWDBExpr&)`	See Section 2.4.2.
`rwdbCast(const RWBDExpr&, const RWDBValue&, const RWDBExpr&, const RWDBExpr&)`	See Section 2.4.2.
`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)`

2.4.2 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.

2.4.3 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 Section 5.3.3, "Outer Joins," in the DB Interface Module User's Guide, for information on constructing outer joins.

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);

The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.