Simple CASE Expressions
This section demonstrates how to construct a simple
CASE expression for a
SELECT statement using
RWDBSimpleCaseExpr. This example generates a category abbreviation based on a video category.
Here is the SQL:
select title,
CASE category
WHEN 'Animated' THEN 'Anim'
WHEN 'Comedy' THEN 'Cmdy'
WHEN 'Drama' THEN 'Drma'
WHEN 'Science Fiction' THEN 'SciFi'
ELSE category
END
from videos
order by title
Now let’s look at the code:
Example 7 – Performing a query with a simple 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"];
const RWDBColumn categoryColumn = videos["category"];
RWDBSimpleCaseExpr simpleExpr(categoryColumn); //1
simpleExpr.when( "Animated", "Anim" ); //2
simpleExpr.when( "Comedy", "Cmdy" );
simpleExpr.when( "Drama", "Drma" );
simpleExpr.when( "Science Fiction", "SciFi" );
simpleExpr.else_(categoryColumn); //3
select << titleColumn //4
<< simpleExpr; //5
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
simpleExpr of
RWDBSimpleCaseExpr, using the column "category" as the input expression against which to compare.
On
//2 and following, use the
when() method to specify WHEN/THEN statements for the
CASE expression. The first argument to
when() defines an
RWDBExpr that is compared against the input expression with which the
RWDBSimpleCaseExpr was constructed. If the comparison is true, then the second argument is used. 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.
In //4 and //5, define the select list to include the "title" and the CASE expression.
