Tips for Simplifying Crosstab Query Statements

Published: 17th February 2011
Views: N/A

After the All About the Crosstab Query article was published, some astute readers pointed out that my SQL statement was more complex than it needed to be. Their suggestions sent me in search of a simplified statement that would yield the same results. There were a couple of ways to shorten and simplify the SQL – in some cases, substantially. We will be reviewing the most effective solutions today, which include using the CASE statement within the Count() function and the specialized WITH ROLLUP GROUP BY Modifier.

The CASE Statement

The CASE statement is a type of conditional construct much like an IF ELSE. It evaluates a search condition and executes the corresponding SQL statement list, depending on which condition evaluates to true. If no search condition matches, the statement list in the ELSE clause is executed instead. Each statement list may consist of one or more statements. Multiple statements are enclosed between the BEGIN and END

I often find discussions about whether SPARQL is difficult to be a bit silly. Not that SPARQL is incredibly easy—although I do find it easy enough as query or scripting languages go—but because any talk of its suitability for your Mom is just a red herring. In a January blog posting titled Hey CNN, SPARQL isn't so difficult, I wrote that as with SQL and other query languages, no one expects end users to type out SPARQL queries, but that someone who already knows a scripting language or two can pick up SPARQL and use it to build new kinds of applications.

I've written an article titled Build Wikipedia query forms with semantic technology to demonstrate how, and it's now live on IBM's developerWorks website. The article walks the reader through the components of two simple form-driven applications: one that queries the Internet Movie Database for the names of actors who have appeared in movies by the two directors whose names you enter on the input form (for example, only Kathleen Turner has appeared in both a Francis Ford Coppola film and a Sofia Coppola film), and another that retrieves nicely-formatted information about recording artist albums from DBpedia.

I tried to make it clear in the article that the cool part of all this is not this relatively new query language, but the existence of these collections of data that can be accessed by a standard query language and the ease with which one can build a query around search terms entered on a form, then send the query off to the server, and then format and display the results in a web page—just as people have done with SQL queries for years now.

I hope I got those points across, and that the article helps more people understand the contributions that SPARQL and linked data can make to useful applications that couldn't exist otherwise.

Report this article Ask About This Article

More to Explore