Users Guide
Defining DataWindow Objects

When you choose Quick Select, the Quick Select dialog box displays. The Tables box lists tables and views in the current database.

Which tables and views display?

The DBMS determines what tables and views display. For some DBMSs, all tables and views display, whether or not you have authorization. If you select a table or view you are not authorized to access, the DBMS issues a message.

For ODBC databases, the tables and views that display depend on the driver for the data source. SQL Anywhere does not restrict the display, so all tables and views display, whether or not you have authorization.

Tables with key relationships

When you select a table, the table's column names display in the Columns box, and any tables having a key relationship with the selected table display in the Tables box. These tables are indented and marked with an arrow to show their relationship to the selected table. You can select any of these related tables if you want to include columns from them in the DataWindow object.

Meaning of the up and down arrows

An arrow displays next to a table to indicate its relationship to the selected table. The arrow always points in the many direction of the relationship—toward the selected table (up) if the selected table contains a foreign key in the relationship and away from the selected table (down) if the selected table contains a primary key in the relationship:

In this preceding illustration, the selected table is sales_order. The Up arrows indicate that a foreign key in the sales_order table is mapped to the primary key in the customer and fin_code tables. The Down arrow indicates that the sales_order_items table contains a foreign key mapped to the primary key in the sales_order table.

How columns from additional tables display

The column names of selected tables display in the Columns box. If you select more than one table, the column names are identified as:

tablename.columnname

For example, department.dept_name and employee.emp_id display when the Employee table and the Department table are selected.

You can select columns from the primary table and from its related tables. Select the table whose columns you want to use in the Tables box, and add columns from the Columns box:

As you select columns, they display in the grid at the bottom of the dialog box in the order in which you select them. If you want the columns to display in a different order in the DataWindow object, select a column name you want to move in the grid and drag it to the new location.

In the grid at the bottom of the Quick Select dialog box, you can specify if you want the retrieved rows to be sorted. As you specify sorting criteria, PowerBuilder builds an ORDER BY clause for the SELECT statement.

Multilevel sorts

You can specify as many columns for sorting as you want. PowerBuilder processes the sorting criteria left to right in the grid: the first column with Ascending or Descending specified becomes the highest level sorting column, the next column with Ascending or Descending specified becomes the next level sorting column, and so on.

If you want to do a multilevel sort that does not match the column order in the grid, drag the columns to the correct order and then specify the columns for sorting.

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.

SQL operators supported in Quick Select

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

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.

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:

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

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:

• You can automatically pop up a window prompting users to specify criteria each time, just before data is retrieved.

  For more information, see Enhancing DataWindow Objects.

• You can place the DataWindow object in query mode using the Modify method.

  For more information, see the section called “Providing query ability to users” in DataWindow Programmers Guide.