Defining queries

A query is a SQL SELECT statement created in the Query painter and saved with a name so that it can be used repeatedly as the data source for a DataWindow object.

Queries save time, because you specify all the data requirements just once. For example, you can specify the columns, which rows to retrieve, and the sorting order in a query. Whenever you want to create a DataWindow object using that data, simply specify the query as the data source.

To define a query:

  1. Select File>New from the menu bar.

  2. In the New dialog box, select the Database tab.

  3. Select the Query icon and click OK.

  4. Select tables in the Select Tables dialog box and click Open.

    You can select columns, define sorting and grouping criteria, define computed columns, and so on, exactly as you do when creating a DataWindow object using the SQL Select data source.

    For more about defining the SELECT statement, see Using SQL Select.

Previewing the query

While creating a query, you can preview it to make sure it is retrieving the correct rows and columns.

To preview a query

  1. Select Design > Preview from the menu bar.

    PowerBuilder retrieves the rows satisfying the currently defined query in a grid-style DataWindow object.

  2. Manipulate the retrieved data as you do in the Database painter in the Output view.

    You can sort and filter the data, but you cannot insert or delete a row or apply changes to the database. For more about manipulating data, see Managing the Database.

  3. When you have finished previewing the query, click the Close button in the PainterBar to return to the Query painter.

Saving the query

To save a query

  1. Select File > Save Query from the menu bar.

    If you have previously saved the query, PowerBuilder saves the new version in the same library and returns you to the Query painter. If you have not previously saved the query, PowerBuilder displays the Save Query dialog box.

  2. Enter a name for the query in the Queries box (see Naming the query).

  3. (Optional) Enter comments to describe the query.

    These comments display in the Library painter. It is a good idea to use comments to remind yourself and others of the purpose of the query.

  4. Specify the library in which to save the query, and click OK.

Naming the query

The query name can be any valid PowerBuilder identifier up to 255 characters. When you name queries, use a unique name to identify each one. A common convention is to use a two-part name: a standard prefix that identifies the object as a query (such as q_) and a unique suffix.For example, you might name a query that displays employee data q_emp_data. For information about PowerBuilder identifiers, see the section called “Identifier names” in PowerScript Reference.

Modifying a query

To modify a query

  1. Select File>Open from the menu bar.

  2. Select the Queries object type and then the query you want to modify, and click OK.

  3. Modify the query as needed.