Mastering Crystal Reports 9 [Electronic resources] نسخه متنی

A Structured Query Language Primer

As the name implies, SQL is used to query a database to ask for specific data to be returned. As a database interaction language, SQL provides other features as well. Here’s a summary of what a generic SQL interface provides:

Data retrieval through the query facility

Calculations (SQL expressions) on stored data

Filtering, sorting, and grouping of data

Creation of the database structure itself

Definition of relationships between data elements

Insertion of new data

Update of existing data

Deletion of existing data

Enforcement of data-integrity constraints

Granting of security through privileges assigned to groups and individuals

Retrieving Data

Crystal Reports and report designers focus on the data-retrieval component of SQL, which is performed by the SELECT statement. This statement is read-only in the sense that executing a SELECT statement does not make any changes to the data or the database. A SELECT statement finds and retrieves columns of data from a database by searching for rows that meet criteria you specify. The basic syntax of a SELECT statement is

SELECT <column(s)> FROM <table>

where column(s) is the actual column name from the database and table is the actual table name. Let’s look at how to use the SELECT statement by working with several business questions on the VistaNations database, which is being accessed as an ODBC data source.

Business Question: In which cities does VistaNation have resorts?

To retrieve the names of all the cities that have resorts in them, the SELECT statement would look like this:

SELECT City FROM Resorts

This statement returns a set of records, also known as a result set, that resembles Chapter 10, “Data Sources and the Database Expert.”

A few things should jump out at you when you view the results of this simple query. First, there are duplicates in the list, and second, the results are not sorted. Also, the bottom of Figure 11.6 shows that we retrieved 201 records. By default in a SELECT statement, records are returned in the order in which they were entered into the database. There are several keywords you can use with a SELECT statement to refine both the number of rows that are returned and the order in which they are returned. Let’s use the DISTINCT keyword to see what difference it makes. The new query is presented below and its results are displayed in Figure 11.7:

SELECT DISTINCT City FROM Resorts

Figure 11.6. Selecting cities

Figure 11.7. Selecting distinct cities

The number of records returned has been reduced to 171 and the list has been sorted. The DISTINCT keyword picks out the first occurrence of a value so that the list contains no duplicates; in addition, it automatically sorts the records in ascending order. This answers the business question a bit better. So the DISTINCT keyword is one way to reduce or refine the number of records returned by your query.

Refining a SELECT Statement

Without the use of keywords to modify the behavior of the SELECT, a query returns every row that matches your criteria. The DISTINCT keyword is one way to limit the rows returned, but it works on only one column. The most powerful way to fine-tune a query is to write a good WHERE clause, which has the general format of

SELECT <column(s)> FROM <table>
WHERE column <operator> value

A WHERE clause filters out data records that do not meet the specified condition, which has the potential to reduce the number of records returned by a query. It sets up a condition that must be true in order for the record to be retrieved from the database. The condition is formulated by comparing a column value to either another column value or a specific value that you provide. A WHERE clause can contain multiple condition statements each separated by the logical conditions AND and OR. Table 11.1 lists the operators that are valid in a WHERE clause.

As an example of a WHERE clause, let’s try to answer a business question for the VistaNations folks.

Business Question: How many five-star resorts does VistaNation have in the United States?

The query makes use of two conditions that must be met in order for a row to be returned in the result set. The results of the query are shown in Figure 11.8.

SELECT COUNT(ResortCode) FROM Resorts
WHERE FiveStarRating = True AND Country = 'US'

Figure 11.8. Using a WHERE clause

This query uses a built-in function called COUNT to operate on the ResortCode column. The result of this query is a single number representing how many rows meet the criteria, in this case 80. Notice in Figure 11.8 that the column name for the result is provided as Expr1000; this is called a SQL expression. Since a SQL expression is a calculation and not an actual column in the table, the column header reflects this. The general syntax for using a built-in SQL function in a SELECT is

SELECT function(column) FROM table

Table 11.2 describes several other popular SQL functions that can be used on column data.

Grouping and Sorting

The result of a SELECT statement is a set of data records. This result set is held in memory in the database until processing is complete and then returned to Crystal Reports. While this temporary result set is in memory, it can be grouped, sorted, and filtered using SQL SELECT keywords. Let’s vary the business question just a little and see how these keywords work.

Business Question: How many five-star resorts are within each state and zip code in the United States? Create the list in reverse order by state.

To following query answers this business question and its results are shown in Figure 11.9:

SELECT COUNT(StateProvince), StateProvince, PostalCode
FROM Resorts
WHERE FiveStarRating = True AND Country = 'US'
GROUP BY PostalCode, StateProvince
ORDER BY StateProvince DESC

Figure 11.9. Grouping and sorting

In this query, two actual columns are retrieved from the table and one aggregate function or SQL expression is used. The results are grouped by state and presented in reverse order by state, but within the state the zip codes are sorted in ascending order. Things are getting pretty complex! Having complete control over the SQL statement generating the data helps you guarantee that you’re answering the business question correctly.

SQL Joins

In Crystal Reports, the Database Expert is used to create joins by visually linking one column to another column. In a SELECT statement, keywords are used to perform joins except for the default join type.

In Chapter 9, “Working with Multiple Tables,” we discussed the concept of inner joins, left outer joins, right outer joins, and full outer joins. Depending on which join types are supported by the data source, join conditions can be included in a SELECT statement. If you don’t specify a join condition when using multiple tables with a SELECT statement, a default inner join is performed, meaning that every record in the first table is joined with every record in the second table if the WHERE clause condition is true.

The use of keywords in a SELECT statement pushes a good deal of processing back onto the database and results in fewer data records being sent over the network wires into Crystal Reports.

A summary of the keywords and the syntax for using them is described in Table 11.3.

ODBC and SQL Statements

ODBC is a middle layer between a requesting application and a data source. The data source has its own proprietary SQL dialect. ODBC has its own version of SQL as well. With each call made to an ODBC data source, ODBC makes data requests using a generic version of SQL that has been designed to work in tandem with the data source being queried. This often results in translations from the SQL statements that you would use directly in the data source versus those that ODBC will use. For instance, in the query that counted resort codes with five-star ratings in the United States, the following two SQL syntaxes should be considered:

Query in Microsoft Access directly:

SELECT COUNT(ResortCode) FROM Resorts
WHERE FiveStarRating = "Yes" AND Country = "US"

Query using ODBC driver to Microsoft Access:

SELECT COUNT(ResortCode) FROM Resorts
WHERE FiveStarRating = True AND Country = 'US'

While both queries return the same result of 51 resorts meeting the criteria, note the syntactic differences that the ODBC driver used, namely:

The FiveStarRating field was treated as a Boolean data type instead of character data.

The string comparison on Country required single quotes instead of double quotes.

The syntax and conversions required depend on the software tool being used, the version of the ODBC driver, and the ODBC support provided by the database vendor. Do not be surprised by syntactic differences like the ones shown here; an ODBC driver is a different access mechanism into the database than the native SQL drivers. Most of the differences are explained by the need for ODBC to service a multitude of data source types while individual native SQL drivers are tuned for exactly one data source.