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:
-
Select File>New from the menu
bar. -
In the New dialog box, select the Database tab.
-
Select the Query icon and click OK.
-
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:
-
Select Design>Preview from the
menu bar.PowerBuilder retrieves the rows satisfying the currently defined
query in a grid-style DataWindow object. -
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 Chapter 16, “Managing the Database.” -
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:
-
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. -
Enter a name for the query in the Queries box
(see “Naming the query”). -
(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. -
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
40 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 PowerScript
Reference.
Modifying a query
To modify a query:
-
Select File>Open from the menu bar.
-
Select the Queries object type and then the query you
want to modify, and click OK. -
Modify the query as needed.