Filtering database data
When creating a JDBC data set, you use a SQL SELECT statement to specify which rows to retrieve from the database. To select only rows that meet certain criteria, add a WHERE clause to the SELECT statement. The WHERE clause consists of the keyword WHERE, followed by a search condition that specifies which rows to retrieve.
For example, the following statement returns only customers from the USA:
SELECT customerName
FROM Customer
WHERE country = 'USA'
As another example, the following statement returns only customers from USA and whose credit limit exceeds $10,000.00:
SELECT customerName
FROM Customer
WHERE country = 'USA'
AND creditLimit > 10000
In the following example, the statement returns all customers from USA or Canada:
SELECT customerName
FROM Customer
WHERE country = 'USA'
OR country = 'Canada'
How to filter the rows to retrieve from a JDBC data source
This procedure assumes that you have already created a JDBC data set using a SQL query or stored procedure.
1 In Data Explorer, double-click the data set to which to add a filter condition.
2 Perform one of the following procedures, depending on whether your data source is JDBC or a JDBC connection for query builder:
*If your data source is JDBC and you used the query editor to type the SQL SELECT statement, in Edit Data Set, add a WHERE clause to the SELECT statement to specify a filter condition.
*If your data source is a JDBC connection for query builder and you used the query builder to create the SQL SELECT statement graphically, select the Conditions tab in the bottom pane to create the filter condition.
1 In Column, select a column from the drop-down list.
2 In Operator, select an operator.
3 In Value, specify the value to search.
4 In AND/OR, optionally select AND or OR to specify another filter condition.
The query builder generates the corresponding WHERE clause in the top pane. Figure 12‑2 shows two filter conditions specified in the query builder.
Figure 12‑2 Filter condition specified in the query builder
For examples and information about the types of filter conditions that you can specify, see the next section.
3 Choose Preview Results to verify that the query returns only the rows that meet the filter condition.