Searched CASE Expressions
The example in this section uses
RWDBSearchedCaseExpr to construct a searched
CASE expression for a
SELECT statement that generates a decade string for each video release date.
Here is the SQL:
select title,
CASE
WHEN 1970 <= yr AND yr <= 1979 THEN '1970s'
WHEN 1980 <= yr AND yr <= 1989 THEN '1980s'
WHEN 1990 <= yr AND yr <= 1999 THEN '1990s'
ELSE 'Unknown Decade'
END
from videos
order by title
Now let’s look at the code:
Example 8 – Performing a query with a searched CASE expression
#include <rw/rstream.h>
#include <rw/db/db.h>
int
main() {
RWDBDatabase myDbase = RWDBManager::database(
"ODBC", // Access Module name
"odbc_dsn", // server name
"user", // user name
"pwd", // password
"DEMO_DB" // database name
);
const RWDBConnection myConnection = myDbase.connection();
const RWDBTable videos = myDbase.table("videos");
RWDBSelector select = myDbase.selector();
const RWDBColumn yearColumn = videos["yr"];
const RWDBColumn titleColumn = videos["title"];
RWDBSearchedCaseExpr searchedExpr; //1
searchedExpr.when( (1970 <= yearColumn) && (yearColumn <= 1979), "1970s" ); //2
searchedExpr.when( (1980 <= yearColumn) && (yearColumn <= 1989), "1980s" );
searchedExpr.when( (1990 <= yearColumn) && (yearColumn <= 1999), "1990s" );
searchedExpr.else_("Unknown Decade"); //3
select << titleColumn //4
<< searchedExpr; //5
select.orderBy(titleColumn);
RWDBReader rdr = select.reader(myConnection);
RWCString title, decade;
while(rdr()) {
rdr >> title >> decade;
std::cout << title << "\t" << decade << std::endl;
}
return 0;
On
//1, create an instance of
RWDBSearchedCaseExpr.
On
//2 and following, use the
when() method to specify WHEN/THEN statements for the CASE expression. The first argument to
when() defines an
RWDBCriterion that, if true, uses the second argument. This pattern is repeated until all required WHEN/THEN statements are defined.
On //3, add an ELSE statement for the CASE expression to specify a fall-through value.
On //4 and //5, define the select list to include the "title" and the CASE expression.
