HomePowerBuilderAppeon Documentation Center

Specifying selection criteria

SQL expression examples

You can enter selection criteria in the grid to specify which rows to retrieve. For example, instead of retrieving data about all employees, you might want to limit the data to employees in Sales and Marketing, or to employees in Sales who make more than $80,000.

As you specify selection criteria, PowerBuilder builds a WHERE clause for the SELECT statement.

To specify selection criteria

  1. Click the Criteria row below the first column for which you want to select the data to retrieve.

  2. Enter an expression, or if the column has an edit style, select or enter a value.

  3. If the column is too narrow for the criterion, drag the grid line to enlarge the column. This enlargement does not affect the column size in a DataWindow object.

  4. Enter additional expressions until you have specified the data you want to retrieve.

    About edit styles

    If a column has an edit style associated with it in the extended attribute system tables (that is, the association was made in the Database painter), if possible, the edit style is used in the grid. Drop-down list boxes are used for columns with code tables and columns using the CheckBox and RadioButton edit styles.

SQL operators supported in Quick Select

You can use these SQL relational operators in the retrieval criteria:

Operator

Meaning

=

Is equal to (default operator)

>

Is greater than

<

Is less than

< >

Is not equal to

> =

Is greater than or equal to

< =

Is less than or equal to

LIKE

Matches this pattern

NOT LIKE

Does not match this pattern

IN

Is in this set of values

NOT IN

Is not in this set of values

Because = is the default operator, you can enter the value 100 instead of = 100, or the value New Hampshire instead of = New Hampshire.

Comparison operators

You can use the LIKE, NOT LIKE, IN, and NOT IN operators to compare expressions.

  • Use LIKE to search for strings that match a predetermined pattern. Use NOT LIKE to find strings that do not match a predetermined pattern. When you use LIKE or NOT LIKE, you can use wildcards:

    The percent sign (%), like the wildcard asterisk (*) used in many applications, matches multiple characters. For example, Good% matches all names that begin with Good.

    The underscore character (_) matches a single character. For example, Good _ _ _ matches all seven-letter names that begin with Good.

  • Use IN to compare and include a value that is in a set of values. Use NOT IN to compare and include values that are not in a set of values. For example, the following clause selects all employees in department 100, 200, or 500: 

    SELECT * from employee
WHERE dept_id IN (100, 200, 500)

  • Using NOT IN in this clause would exclude employees in those departments.

Connection operators

You can use the OR and AND logical operators to connect expressions.

PowerBuilder makes some assumptions based on how you specify selection criteria. When you specify:

  • Criteria for more than one column on one line

    PowerBuilder assumes a logical AND between the criteria. A row from the database is retrieved if all criteria in the line are met.

  • Two or more lines of selection criteria

    PowerBuilder assumes a logical OR. A row from the database is retrieved if the criterion in any of the lines is met.

To override these defaults, begin an expression with the AND or OR operator:

Operator

Meaning

OR

The row is selected if one expression OR another expression is true

AND

The row is selected if one expression AND another expression are true


This technique is particularly handy when you want to retrieve a range of values in a column. See example 6 below.

SQL expression examples

The first six examples in this section all refer to a grid that contains three columns from the employee table: emp_id, dept_id, and salary.

Example 1

The expression <50000 in the Criteria row in the salary column in the grid retrieves information for employees whose salaries are less than $50,000.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE employee.salary < '50000'

Example 2

The expression 100 in the Criteria row in the DeptId column in the grid retrieves information for employees who belong to department 100.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE employee.dept_id ='100'

Example 3

The expression >300 in the Criteria row in the EmpId column and the expression <50000 in the Criteria row in the Salary column in the grid retrieve information for any employee whose employee ID is greater than 300 and whose salary is less than $50,000.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE (employee.emp_id >'300') AND
   employee.salary <'50000'

Example 4

The expressions 100 in the Criteria row and >300 in the Or row for the DeptId column, together with the expression <50000 in the Criteria row in the Salary column, retrieve information for employees who belong to:

  • Department 100 and have a salary less than $50,000

    or

  • A department whose ID is greater than 300, no matter what their salaries


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE (employee.dept_id = '100') AND 
   (emplyee.salary < '50000')OR 
   (employee.dept_id > '300')

Example 5

The expression IN(100,200) in the Criteria row in the DeptId column in the grid retrieves information for employees who are in department 100 or 200.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE employee.dept_id IN ('100,200')

Example 6

This example shows the use of the word AND in the Or criteria row. In the Criteria row, >=500 is in the EmpId column and >=30000 is in the Salary column. In the Or row, AND <=1000 is in the EmpId column and AND <=50000 is in the Salary column. These criteria retrieve information for employees who have an employee ID from 500 to 1000 and a salary from $30,000 to $50,000.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_id, 
   employee.dept_id, 
   employee.salary
FROM employee
WHERE (((employee.emp_id >='500') AND
   (employee.salary >='30000') AND
   (employee.emp_id <='1000') AND
   (employee.salary <='50000')))

Example 7

In a grid with three columns: emp_last_name, emp_first_name, and salary, the expressions LIKE C% in the Criteria row and LIKE G% in the Or row in the emp_last_name column retrieve information for employees who have last names that begin with C or G.


The SELECT statement that PowerBuilder creates is:

SELECT employee.emp_last_name, 
   employee.emp_first_name, 
   employee.salary
FROM employee
WHERE (((employee.emp_last_name LIKE 'C%'))OR
   ((employee.emp_last_name LIKE 'G%')))

Providing SQL functionality to users

You can allow your users to specify selection criteria in a DataWindow object using these techniques at runtime:

Prev  Up  Next
Specifying sorting criteria  Home  Using SQL Select