Derived Tables
The
RWDBTable interface supports derived tables representing a SQL query. The results from a derived table can be used in queries in the same way as a persistent table. The tabular result set produced from the execution of the SQL query constitutes the contents of the derived table. The derived table becomes part of the SQL that defines and uses it. It is created on the fly by the database while executing the SQL.
Derived tables can be used in the FROM clause of a SQL query in the same way a persistent database table is used. As a derived table does not physically exist in the database, it must have a table tag.
To obtain a derived table, use API
RWDBDatabase::table(const RWDBSelectorBase&). This method is similar to that which obtains the handle to the persistent database table, except that, rather than the name of the table, this method accepts the
RWDBSelectorBase instance representing the SQL query that creates the derived table. For example:
RWDBTable derivedTab = myDbase.table(select);
where
myDbase is a valid
RWDBDatabase object and select is a
RWDBSelectorBase instance. The
derivedTab instance can now be used similarly to a persistent table in a SQL query.
Following is an example of using a derived table. It assumes that
myDbase is a valid
RWDBDatabase object and
myConn a valid
RWDBConnection object.
RWDBTable tabLoc = myDbase.table("location"); //1
RWDBTable tabEmp = myDbase.table("employee"); //2
RWDBSelector derivedSel = myDbase.selector(); //3
derivedSel << tabLoc; //4
derivedSel.where(tabLoc["country"] == "ABCD"); //5
RWDBTable tabLocDerived = myDbase.table(derivedSel); //6
RWDBSelector sel = myDbase.selector(); //7
sel << tabEmp["id"] << tabEmp["name"] << tabLocDerived["name"]; //8
sel.where(tabEmp["locid"] == tabLocDerived["id"]); //9
RWDBResult result = sel.execute(myConn); //10
Lines
//1 and
//2 create handles to the persistent tables –
location and
employee respectively. Line
//3 through
//5 create an
RWDBSelector object that represents a SQL that fetches all records where the country is ABCD. The
RWDBSelector is explained in detail in
Chapter , Selecting Data. Line
//6 defines a derived table encapsulating the above SQL query.
Lines
//7 through
//9 define an
RWDBSelector that uses the derived table created in
//6, as well as the persistent table
employee, to fetch employee information for all employees in the country ABCD. Finally, line
//10 executes the query on the supplied connection.
By encapsulating the derived table functionality in the
RWDBTable interface, both persistent tables and derived tables can be used with one consistent API.
