OS/2 Shareware BBS: 18 REXX/18-REXX.zip/vxcliser.zip/VRCS.1/QUERYED.HLP

OS/2 Help File | 1994-12-23 | 41KB | 1,439 lines

ΓòÉΓòÉΓòÉ 1. Aggregate ΓòÉΓòÉΓòÉ

Use this page to add aggregate functions to the SQL query. The Selected
aggregate functions list shows aggregate functions that are currently part of
the query. Aggregate functions are listed in the SELECT clause of the final SQL
statement.

Help for tasks:

   Adding an aggregate function to the query
   Removing an aggregate function from the query

Window objects:

   Selected aggregate functions
   Remove
   New aggregate function
   Edit
   Add
   Modify


ΓòÉΓòÉΓòÉ 1.1. Adding an aggregate function to the query ΓòÉΓòÉΓòÉ

To add a new aggregate function to the query, type a new aggregate function in
the New aggregate function field, then click the Add button.


ΓòÉΓòÉΓòÉ 1.2. Removing an aggregate function from the query ΓòÉΓòÉΓòÉ

To remove an aggregate function from the query, click on the function you want
to remove in the Selected aggregate functions list, then click the Remove
button.


ΓòÉΓòÉΓòÉ 1.3. Selected aggregate functions ΓòÉΓòÉΓòÉ

The Selected aggregate function list shows the aggregate functions that are
currently part of the SQL query. When you click on a function in this list, it
is copied to the New aggregate function field.


ΓòÉΓòÉΓòÉ 1.4. New aggregate function ΓòÉΓòÉΓòÉ

Type the new aggregate function into this entry field. When you are finished,
click the Add button. Enter only one aggregate function at a time.


ΓòÉΓòÉΓòÉ 1.5. Add button ΓòÉΓòÉΓòÉ

Click the Add button to add the new aggregate function to the Selected
aggregate functions list. You must explicitly add a new function to the list in
this way for it to become part of the SQL query.


ΓòÉΓòÉΓòÉ 1.6. Modify button ΓòÉΓòÉΓòÉ

Click the Modify button to replace the selected aggregate function with the one
in the New aggregate function field.


ΓòÉΓòÉΓòÉ 1.7. Remove button ΓòÉΓòÉΓòÉ

Click the Remove button to remove highlighted aggregate functions from the
Selected aggregate functions list.


ΓòÉΓòÉΓòÉ 1.8. Edit button ΓòÉΓòÉΓòÉ

Click the Edit button to invoke an editor for the current aggregate function.
The editor makes it easy for you to build the aggregate function by showing you
column names and commonly used functions and operators in a calculator format.


ΓòÉΓòÉΓòÉ 2. Columns ΓòÉΓòÉΓòÉ

Use this page to select the column you want to use in the SQL query. Columns in
the Selected columns list are included in the SELECT clause of the final SQL
statement.

Help for tasks:

   Adding a column to the query
   Removing a column from the query

Window objects:

   Selected columns
   Remove
   Distinct
   Available columns
   Add


ΓòÉΓòÉΓòÉ 2.1. Adding a column to the query ΓòÉΓòÉΓòÉ

You can add a column to the Selected columns list by doing any of the
following:

o Click on a column in the Available columns list, then click the Add button.

o Double click on the column name in the Available columns list that you want
  to add.

o Drag the column name you want to add from the Available columns list to the
  Selected columns list.


ΓòÉΓòÉΓòÉ 2.2. Removing a column from the query ΓòÉΓòÉΓòÉ

You can remove a column from the Selected columns list by doing any of the
following:

o Click on a column in the Selected columns list, then click the Remove button.

o Drag the column name you want to remove from the Selected columns list to the
  Available columns list.


ΓòÉΓòÉΓòÉ 2.3. Selected columns list ΓòÉΓòÉΓòÉ

The Selected columns list shows the currently selected columns. These columns
will appear after the SELECT keyword in the SQL statement.


ΓòÉΓòÉΓòÉ 2.4. Remove button ΓòÉΓòÉΓòÉ

Clicking this button removes the highlighted column names from the Selected
columns list.


ΓòÉΓòÉΓòÉ 2.5. Distinct check box ΓòÉΓòÉΓòÉ

If checked, duplicate rows are eliminated from the final result table.


ΓòÉΓòÉΓòÉ 2.6. Available columns list ΓòÉΓòÉΓòÉ

The Available columns list shows the tables and columns from which you can
choose. To see a table's columns, click on the [+] symbol beside the table
name. Double click on a column name to add it to the Selected columns list.
Double click on a table name to add all columns in that table to the Selected
columns list. You can also drag table and column names to the Selected columns
list.


ΓòÉΓòÉΓòÉ 2.7. Add button ΓòÉΓòÉΓòÉ

Clicking the Add button adds the highlighted column or table name from the
Available columns list to the Selected columns list.


ΓòÉΓòÉΓòÉ 3. Connection editor ΓòÉΓòÉΓòÉ

Use the connection editor to set commonly used properties of a connection
object.

Window objects:

   Data source
   Database
   Find
   Userid
   Password
   Connection parameters
   OK
   Cancel


ΓòÉΓòÉΓòÉ 3.1. Data source ΓòÉΓòÉΓòÉ

The Data source field shows the type of database that the connection object
will try to use. The options are:

WATCOM SQL
          WATCOM SQL for OS/2

IBM DB2/2
          IBM Database 2/2

ODBC
          Any database with an Open Database Connectivity (ODBC) interface


ΓòÉΓòÉΓòÉ 3.2. Database ΓòÉΓòÉΓòÉ

Enter the name of the database file that the connection object should use in
the Database field.

If you leave this field blank, the connection object will prompt you for the
database name when its Connect method is invoked.

To search for databases, click the Find button.


ΓòÉΓòÉΓòÉ 3.3. Find ΓòÉΓòÉΓòÉ

Press the Find button to open a file dialog that will help you select a
database. If the Data source is WATCOM SQL or ODBC, the button will display a
file selection dialog, in which you can pick a database file. If Data source is
IBM DB2/2, a window with a list of databases will be displayed.


ΓòÉΓòÉΓòÉ 3.4. Userid ΓòÉΓòÉΓòÉ

You must identify yourself to the database before you can access the data it
contains. The Userid field contains the user identifier the object will use
when connecting to the database.

If you leave this field blank, the connection object will prompt you for a
userid when its Connect method is invoked.


ΓòÉΓòÉΓòÉ 3.5. Password ΓòÉΓòÉΓòÉ

You must identify yourself to the database before you can access the data it
contains. The Password field contains the password for the user named in the
Userid field.

If you leave this field blank, the connection object will prompt you for a
password when its Connect method is invoked. If you type a password in this
field, it will be displayed as a number of asterisks.


ΓòÉΓòÉΓòÉ 3.6. Connection parameters ΓòÉΓòÉΓòÉ

The Connection parameters field contains extra connection information. The
format of this string depends on the database you are using. Consult the
documentation for your database for more information.


ΓòÉΓòÉΓòÉ 3.7. OK ΓòÉΓòÉΓòÉ

Click the OK button to close the Connection editor and save the changes you
have made.


ΓòÉΓòÉΓòÉ 3.8. Cancel ΓòÉΓòÉΓòÉ

Click the Cancel button to close the Connection editor without saving the
changes you have made.


ΓòÉΓòÉΓòÉ 4. Where ΓòÉΓòÉΓòÉ

Use this page to add criteria to the SQL query. Criteria describe restrictions
on the rows that you want to include in the result table. Criteria are listed
in the WHERE clause of the final SQL statement.

Help for tasks:

   Adding a new criteria
   Removing a criteria

Window objects:

   Criteria
   Remove
   New criteria
   AND
   OR
   Edit
   Add
   Modify


ΓòÉΓòÉΓòÉ 4.1. Adding a new criteria ΓòÉΓòÉΓòÉ

To add a new criteria to the query, type a new criteria in the New criteria
field, then click the Add button.


ΓòÉΓòÉΓòÉ 4.2. Removing a criteria ΓòÉΓòÉΓòÉ

To remove a criteria from the query, click on the expression you want to remove
in the Criteria list, then click the Remove button.


ΓòÉΓòÉΓòÉ 4.3. Criteria ΓòÉΓòÉΓòÉ

The Criteria list shows the criteria that are currently part of the SQL query.
Criteria describe restrictions on the rows that you want to include in the
result table.


ΓòÉΓòÉΓòÉ 4.4. New criteria ΓòÉΓòÉΓòÉ

Type the new aggregate function into this entry field. When you are finished,
click the Add button. Enter only one aggregate function at a time.


ΓòÉΓòÉΓòÉ 4.5. Remove ΓòÉΓòÉΓòÉ

Click the Remove button to remove highlighted criteria from the Criteria list.


ΓòÉΓòÉΓòÉ 4.6. AND ΓòÉΓòÉΓòÉ

If the And radio button is set when you click the Add button, the new criteria
will be connected to the other criteria with an AND conjunction.


ΓòÉΓòÉΓòÉ 4.7. OR ΓòÉΓòÉΓòÉ

If the Or radio button is set when you click the Add button, the new criteria
will be connected to the other criteria with an OR conjunction.


ΓòÉΓòÉΓòÉ 4.8. Add ΓòÉΓòÉΓòÉ

Click the Add button to add the new criteria to the Criteria. You must
explicitly add a new function to the list in this way for it to become part of
the SQL query. The new criteria will be linked to the existing criteria with
either the AND or OR keyword, depending on whether the AND or the OR radio
button is set.


ΓòÉΓòÉΓòÉ 4.9. Edit ΓòÉΓòÉΓòÉ

Click the Edit button to invoke an editor for the current criteria. The editor
makes it easy for you to build the criteria by showing you column names and
commonly used functions and operators in a calculator format.


ΓòÉΓòÉΓòÉ 4.10. Modify ΓòÉΓòÉΓòÉ

Click the Modify button to replace the selected criteria with the one in the
New criteria field.


ΓòÉΓòÉΓòÉ 5. Expression editor ΓòÉΓòÉΓòÉ

Use this page to build expressions for aggregate functions or column criteria.
Double click on a column or function name to add it to the expression. Click on
any of the calculator buttons to add the button's text to the expression.

Help on expressions:

   WATCOM SQL expressions
   IBM DB2/2 expressions

Help on functions:

   AVG() function
   COUNT() function
   MAX() function
   MIN() function
   SUM() function
   WATCOM SQL functions
   DB2/2 functions

Window objects:

   Expression
   Columns
   Functions
   Calculator buttons
   OK
   Clear
   Cancel


ΓòÉΓòÉΓòÉ 5.1. AVG() function ΓòÉΓòÉΓòÉ

Syntax:

AVG( numeric_expression )

The AVG() function returns the average of numeric_expression for each group of
rows. If a group contains no rows, the function returns the NULL value.


ΓòÉΓòÉΓòÉ 5.2. COUNT() function ΓòÉΓòÉΓòÉ

Syntax:

COUNT( * )
COUNT( expression )

The first form of this function returns the number of rows in each group. The
second form returns the number of rows in each group where expression is not
the NULL value.


ΓòÉΓòÉΓòÉ 5.3. MAX() function ΓòÉΓòÉΓòÉ

Syntax:

MAX( expression )

The MAX() function returns the maximum expression value found in a each group
of rows. The function returns the NULl value for groups containing no rows.


ΓòÉΓòÉΓòÉ 5.4. MIN() function ΓòÉΓòÉΓòÉ

Syntax:

MIN( expression )

The MIN() function returns the minimum expression value found in a each group
of rows. The function returns the NULl value for groups containing no rows.


ΓòÉΓòÉΓòÉ 5.5. SUM() function ΓòÉΓòÉΓòÉ

Syntax:

SUM( expression )

The SUM() function returns the total of expression for each group of rows. The
function returns 0 if a group contains no rows.


ΓòÉΓòÉΓòÉ 5.6. WATCOM SQL functions ΓòÉΓòÉΓòÉ

Click on the following topics for more information about WATCOM SQL functions:

Expressions
Functions


ΓòÉΓòÉΓòÉ 5.7. IBM DB2/2 functions ΓòÉΓòÉΓòÉ

Click on the following topics for more information about IBM DB2/2 functions:

Expressions
Column functions
Scalar functions


ΓòÉΓòÉΓòÉ 5.8. Expression ΓòÉΓòÉΓòÉ

The Expression entry field contains the expression that you are currently
editing. While you can type in this field directly, you can also click on the
calculator buttons and the column and function lists to add text to the entry
field. Text will is always added at the current insertion point.


ΓòÉΓòÉΓòÉ 5.9. Columns ΓòÉΓòÉΓòÉ

The Columns list displays the names of all columns in all selected tables.

Double clicking on a column name adds it to the expression at the current
insertion point. If text in the Expression field is selected, double clicking
on a column name will replace the selected text.


ΓòÉΓòÉΓòÉ 5.10. Functions ΓòÉΓòÉΓòÉ

The Functions list displays a list of functions that are valid for the database
you are using.

Double clicking on a function adds it to the expression at the current
insertion point. If text in the Expression field is selected, double clicking
on a function name will replace the selected text.

Note:  Your database may support other functions that are not listed here.
Consult your database documentation for a complete list of functions.


ΓòÉΓòÉΓòÉ 5.11. Calculator buttons ΓòÉΓòÉΓòÉ

There are a number of calculator buttons on the expression editor window:
digits, operators, and some keywords. When you press any of these buttons, the
button text is added to the expression at the current insertion point. If text
in the Expression field is selected, pressing a calculator button will replace
the selected text.


ΓòÉΓòÉΓòÉ 5.12. OK button ΓòÉΓòÉΓòÉ

Pressing the OK button accepts changes made to the expression, and closes the
expression editor window.


ΓòÉΓòÉΓòÉ 5.13. Clear button ΓòÉΓòÉΓòÉ

Pressing the Clear button erases the contents of the Expression field.


ΓòÉΓòÉΓòÉ 5.14. Cancel button ΓòÉΓòÉΓòÉ

Pressing the Cancel button closes the expression editor window, and throws away
all changes that have been made to the expression.


ΓòÉΓòÉΓòÉ 6. Group by ΓòÉΓòÉΓòÉ

Use this page to group rows in the result table based on similar column values.
Once columns are grouped, you can then apply aggregate functions to the groups
to get meaningful results. Columns in the Grouped columns list are included in
the GROUP BY clause of the final SQL statement.

Help for tasks:

   Adding a column to the group list
   Removing a column from the grouped list

Window objects:

   Grouped columns
   Remove
   Available columns
   Add


ΓòÉΓòÉΓòÉ 6.1. Adding a column to the group list ΓòÉΓòÉΓòÉ

You can add a column to the Grouped columns list by doing any of the following:

o Click on a column in the Available columns list, then click the Add button.

o Double click on the column name in the Available columns list that you want
  to add.

o Drag the column name you want to add from the Available columns list to the
  Grouped columns list.


ΓòÉΓòÉΓòÉ 6.2. Removing a column from the group list ΓòÉΓòÉΓòÉ

You can remove a column from the Grouped columns list by doing any of the
following:

o Click on a column in the Grouped columns list, then click the Remove button.

o Double click on the column name in the Grouped columns list that you want to
  remove.

o Drag the column name you want to remove from the Grouped columns list to the
  Available columns list.


ΓòÉΓòÉΓòÉ 6.3. Grouped columns list ΓòÉΓòÉΓòÉ

The Grouped columns list contains the columns by which rows will be grouped. To
remove a column from the list, either double click on an column name, or drag
it onto the Available columns list.


ΓòÉΓòÉΓòÉ 6.4. Remove button ΓòÉΓòÉΓòÉ

Clicking this button removes the highlighted column names from the Grouped
columns list.


ΓòÉΓòÉΓòÉ 6.5. Available columns list ΓòÉΓòÉΓòÉ

The Available columns list shows the tables and columns from which you can
choose. To see a table's columns, click on the [+] symbol beside the table
name. Double click on a column name to add it to the Grouped columns list.
Double click on a table name to add all columns in that table to the Grouped
columns list. You can also drag table and column names to the Grouped columns
list.


ΓòÉΓòÉΓòÉ 6.6. Add button ΓòÉΓòÉΓòÉ

Clicking the Add button adds the highlighted column or table name from the
Available columns list to the Grouped columns list.


ΓòÉΓòÉΓòÉ 7. Having ΓòÉΓòÉΓòÉ

Use this page to assign conditions to the row groupings set up on the Group by
page. Columns in the Group condition list are included in the HAVING clause of
the final SQL statement.

Help for tasks:

   Adding a group condition
   Removing a group condition

Window objects:

   Group condition
   Remove
   Condition
   AND
   OR
   Edit
   Add
   Modify


ΓòÉΓòÉΓòÉ 7.1. Adding a group condition ΓòÉΓòÉΓòÉ

To add a new group condition to the query, type a new condition in the
Condition field, then click the Add button.


ΓòÉΓòÉΓòÉ 7.2. Removing a group condition ΓòÉΓòÉΓòÉ

To remove a group condition from the query, click on the function you want to
remove in the Group condition list, then click the Remove button.


ΓòÉΓòÉΓòÉ 7.3. Group condition ΓòÉΓòÉΓòÉ

The Group conditions list shows the group conditions that are currently part of
the SQL query. When you click on a condition, it is copied to the Condition
field.


ΓòÉΓòÉΓòÉ 7.4. Remove ΓòÉΓòÉΓòÉ

Clicking the Remove button deletes the highlighted group conditions from the
Group condition list.


ΓòÉΓòÉΓòÉ 7.5. Condition ΓòÉΓòÉΓòÉ

Type the new group condition into this field. When you are finished, click the
Add button. Enter only one condition at a time.


ΓòÉΓòÉΓòÉ 7.6. AND ΓòÉΓòÉΓòÉ

If the And radio button is set when you click the Add button, the new condition
will be connected to the other conditions with an AND conjunction.


ΓòÉΓòÉΓòÉ 7.7. OR ΓòÉΓòÉΓòÉ

If the Or radio button is set when you click the Add button, the new condition
will be connected to the other conditions with an OR conjunction.


ΓòÉΓòÉΓòÉ 7.8. Edit ΓòÉΓòÉΓòÉ

Click the Edit button to invoke an editor for the new condition. The editor
makes it easy for you to build the condition by showing you column names and
commonly used functions and operators in a calculator format.


ΓòÉΓòÉΓòÉ 7.9. Add ΓòÉΓòÉΓòÉ

Click the Add button to add the new condition to the Group condition list. You
must explicitly add a new condition to the list in this way for it to become
part of the SQL query.


ΓòÉΓòÉΓòÉ 7.10. Modify ΓòÉΓòÉΓòÉ

Click the Modify button to change the selected condition to that contained in
the Condition field.


ΓòÉΓòÉΓòÉ 8. Joins ΓòÉΓòÉΓòÉ

Use this page to specify how you want to join together the tables you have
selected. Information on this page is reflected in the FROM clause of the final
SQL statement.

Help for tasks:

   Adding a join
   Removing a join

Window objects:

   Joins
   Remove
   Table 1
   Role 1
   Table 2
   Role 2
   Type
   Add
   Modify


ΓòÉΓòÉΓòÉ 8.1. Adding a join ΓòÉΓòÉΓòÉ

To add a new join follow these steps:

 1. Select the tables you want to join in the Table 1 and Table 2 lists.

 2. Choose the type of join from the Type list.

 3. Click the Add button.

The names of the tables and the join type will be added to the Joins list.


ΓòÉΓòÉΓòÉ 8.2. Removing a join ΓòÉΓòÉΓòÉ

To remove a join, click on the join in the Joins list, then click the Remove
button.


ΓòÉΓòÉΓòÉ 8.3. Join ΓòÉΓòÉΓòÉ

The Join list displays all joins that are part of the SQL query.


ΓòÉΓòÉΓòÉ 8.4. Table 1 ΓòÉΓòÉΓòÉ

The Table 1 list shows the tables that have been selected for the SQL query.
Choose a table from this list when you add a new join.


ΓòÉΓòÉΓòÉ 8.5. Role 1 ΓòÉΓòÉΓòÉ

The Role 1 list shows role names of foreign keys.


ΓòÉΓòÉΓòÉ 8.6. Role 2 ΓòÉΓòÉΓòÉ

The Role 2 list shows role names of foreign keys.


ΓòÉΓòÉΓòÉ 8.7. Table 2 ΓòÉΓòÉΓòÉ

The Table 2 list shows the tables that have been selected for the SQL query.
Choose a table from this list when you add a new join.


ΓòÉΓòÉΓòÉ 8.8. Type ΓòÉΓòÉΓòÉ

The Type list shows all of the possible join types. When you are adding a new
join, you must select one of these types.


ΓòÉΓòÉΓòÉ 8.9. Remove ΓòÉΓòÉΓòÉ

Clicking the Remove button deletes the highlighted join from the Joins list.


ΓòÉΓòÉΓòÉ 8.10. Add ΓòÉΓòÉΓòÉ

Clicking the Add button adds a new join to the Joins list. In the Joins list,
you will see a new line of the form:

table1 joinType table2

where table1 is the table name from Table 1 list, table2 is the table name from
Table 2 list, and joinType is the  type of join selected in Type list.


ΓòÉΓòÉΓòÉ 8.11. Modify ΓòÉΓòÉΓòÉ

Clicking the Modify button replaces the selected join with the one described in
the Table 1, Role 1, Table 2, Role 2, and Type fields.


ΓòÉΓòÉΓòÉ 9. Join conditions ΓòÉΓòÉΓòÉ

Use this page to specify the way in which tables should be joined. You do this
by defining an equality between columns in the two joined tables. Join
conditions are listed in the WHERE clause of the final SQL statement.

Help for tasks:

   Adding a join condition
   Removing a join condition

Window objects:

   Join
   Conditions
   Remove
   And
   Or
   New condition
   Edit
   Add
   Modify


ΓòÉΓòÉΓòÉ 9.1. Adding a join condition ΓòÉΓòÉΓòÉ

To add a new join condition for the selected join, enter a condition in the New
condition field, then press the Add button.


ΓòÉΓòÉΓòÉ 9.2. Removing a join condition ΓòÉΓòÉΓòÉ

To remove a join condition, click on the condition in the Conditions list, then
click the Remove button.


ΓòÉΓòÉΓòÉ 9.3. Join ΓòÉΓòÉΓòÉ

The Join list contains all joins. When you select a join, its join conditions
are shown in the Conditions list.


ΓòÉΓòÉΓòÉ 9.4. Conditions ΓòÉΓòÉΓòÉ

The Conditions list shows the current conditions for the selected join. When
you select a condition it is also copied into the New condition field.


ΓòÉΓòÉΓòÉ 9.5. Remove ΓòÉΓòÉΓòÉ

Clicking the Remove button deletes the highlighted join conditions from the
Conditions list.


ΓòÉΓòÉΓòÉ 9.6. And ΓòÉΓòÉΓòÉ

If the And radio button is set when you click the Add button, the new condition
will be connected to the other conditions with an AND conjunction.


ΓòÉΓòÉΓòÉ 9.7. Or ΓòÉΓòÉΓòÉ

If the Or radio button is set when you click the Add button, the new condition
will be connected to the other conditions with an OR conjunction.


ΓòÉΓòÉΓòÉ 9.8. New condition ΓòÉΓòÉΓòÉ

Type new conditions in the New condition field. You must press the Add button
to add the new condition to the query.


ΓòÉΓòÉΓòÉ 9.9. Edit ΓòÉΓòÉΓòÉ

Click the Edit button to invoke an editor for the new condition. The editor
makes it easy for you to build the condition by showing you column names and
commonly used functions and operators in a calculator format.


ΓòÉΓòÉΓòÉ 9.10. Add ΓòÉΓòÉΓòÉ

Clicking the Add button adds the new join condition to the Conditions list.


ΓòÉΓòÉΓòÉ 9.11. Modify ΓòÉΓòÉΓòÉ

Clicking the Modify button replaces the selected join condition with the one in
the New condition field.


ΓòÉΓòÉΓòÉ 10. Query editor options ΓòÉΓòÉΓòÉ

Use this dialog to configure Query editor options.

List box font
Change font
Stay connected
Fill table list on startup
Use owner names in generated SQL
Quote owner, table, and column names
OK
Cancel
Default


ΓòÉΓòÉΓòÉ 10.1. List box font ΓòÉΓòÉΓòÉ

The List box font holds the name of the font that is used for all list boxes
and multiline entry fields in the Connection, Query, and SQL editors. The font
has usual VX-REXX font syntax: pointSize.fontName (eg. "10.Helv")

To choose a new font, type in a new font string, or press the Change font
button. The default setting is <default>.


ΓòÉΓòÉΓòÉ 10.2. Stay connected ΓòÉΓòÉΓòÉ

Check this box if you want the Query Editor to leave the connection object
connected when the Query Editor closes. Setting this option will result in
better performance if you tend to open and close the Query Editor frequently
without changing the connection object's parameters. The default for this
option is off, meaning that the Query Editor will close the connection when the
editor closes.


ΓòÉΓòÉΓòÉ 10.3. Change font ΓòÉΓòÉΓòÉ

Press the Change font button to select a new list box font. A font selection
dialog will be displayed.


ΓòÉΓòÉΓòÉ 10.4. Fill table list on startup ΓòÉΓòÉΓòÉ

If the Fill table list on startup box is checked, the SQL editor will fill the
Available tables list with the names of all tables in the database when the SQL
editor is opened. If the box is not checked, the user must explicitly click the
Refresh list button or press the Enter key while editing the Table pattern
field to fill the Available tables list.

If your database contains hundreds of tables, you may want to turn this option
off to reduce the SQL editor's start up time.

By default, this box is not checked.


ΓòÉΓòÉΓòÉ 10.5. Use table owner names in generated SQL ΓòÉΓòÉΓòÉ

If the Use table owner names in generated SQL box is set, the SQL editor will
use fully qualified table names in the SQL SELECT statement that it generates.
A fully qualified name has the form:

ownerName.tableName

If the box is not set, the SQL editor will not include the owner name in the
generated SQL.

By default, this box is checked.


ΓòÉΓòÉΓòÉ 10.6. Quote owner, table and column names ΓòÉΓòÉΓòÉ

If the Quote owner, table, and column names box is set, the SQL editor will
enclose all table owner names, table names, and column names in quotation marks
in the SQL it generates.

Some databases such as dBase do not accept quoted names. If you are using a
database that does not take quoted names, uncheck this box.

By default this box is checked.


ΓòÉΓòÉΓòÉ 10.7. OK ΓòÉΓòÉΓòÉ

Click the OK button to save the new options and close the window.


ΓòÉΓòÉΓòÉ 10.8. Cancel ΓòÉΓòÉΓòÉ

Click the Cancel button to close the window without saving changes.


ΓòÉΓòÉΓòÉ 10.9. Default ΓòÉΓòÉΓòÉ

Click the Default button to restore the options to their default settings.
These settings are:

o Fill the table list on startup
o Use owner names


ΓòÉΓòÉΓòÉ 11. Query editor ΓòÉΓòÉΓòÉ

Use the query editor to set properties of a query object.

Window objects:

   Query name
   Connection object
   Settings
   SQL statement
   Edit
   Load
   Save as
   Test
   No bound objects
   Bound container
   Bound text boxes
   Movement buttons
   Update buttons
   Search buttons
   OK
   Cancel
   Options


ΓòÉΓòÉΓòÉ 11.1. Query name ΓòÉΓòÉΓòÉ

The name of the query object is displayed in this entry field. You can change
the name by typing directly in this field.


ΓòÉΓòÉΓòÉ 11.2. Connection object ΓòÉΓòÉΓòÉ

Each query object must be associated with a connection object. This field shows
the currently selected connection object. To change the object, type in the
name of a new connection object or choose an item from the drop down list.


ΓòÉΓòÉΓòÉ 11.3. Settings ΓòÉΓòÉΓòÉ

Click the Settings button to view and edit the database specific properties of
the selected connection object.


ΓòÉΓòÉΓòÉ 11.4. SQL statement ΓòÉΓòÉΓòÉ

The SQL statement field shows the SQL statement that is stored in the query
object. You can type in this field directly, or can press the Edit button to
invoke the SQL editor.


ΓòÉΓòÉΓòÉ 11.5. Edit ΓòÉΓòÉΓòÉ

Pressing the Edit button opens the SQL editor. The SQL editor is a tool that
lets you compose and test a SQL SELECT statement.


ΓòÉΓòÉΓòÉ 11.6. Load ΓòÉΓòÉΓòÉ

Click the Load button to replace the current SQL SELECT statement with one
stored in a disk file. You will be prompted for the name of the file.


ΓòÉΓòÉΓòÉ 11.7. Save as ΓòÉΓòÉΓòÉ

Click the Save as button to write the current SQL statement to a disk file. You
will be prompted for the name of the file.


ΓòÉΓòÉΓòÉ 11.8. Test ΓòÉΓòÉΓòÉ

Click the Test button to modify and test your SQL statement. The Query editor
will open a resizeable window which contains the query and its results, similar
to the Test page page. The Test window is an alternative to using the SQL
editor.


ΓòÉΓòÉΓòÉ 11.9. No bound objects ΓòÉΓòÉΓòÉ

Set the No bound objects radio button if you do not want the Query editor to
generate any bound objects or push buttons. If this radio button is set, the
Movement buttons, Update buttons, and Search buttons check boxes are ignored.


ΓòÉΓòÉΓòÉ 11.10. Bound container ΓòÉΓòÉΓòÉ

If the Bound container button is set, a bound container will be generated on
the same window as the query object. The container will be in detail view, and
will show a number of rows at once. If the Update buttons box is checked, push
buttons will also be generated.


ΓòÉΓòÉΓòÉ 11.11. Bound text boxes ΓòÉΓòÉΓòÉ

If the Bound text boxes button is set, a number of bound entry fields will be
generated on the same window as the query object. One entry field will be
created for each column in the result table. Each field will be labeled with
the name of the column. If the Movement buttons, Update buttons, or Search
buttons boxes are checked, push buttons will also be generated.


ΓòÉΓòÉΓòÉ 11.12. Movement buttons ΓòÉΓòÉΓòÉ

When the Movement buttons box is checked, the Query editor will automatically
generate push buttons on the query window. The generated buttons let the user
do the following:

o Move to the first row
o Move to the previous row
o Move to the next row
o Move to the last row

The Movement buttons is ignored if either No bound objects or Bound container
are set.


ΓòÉΓòÉΓòÉ 11.13. Update buttons ΓòÉΓòÉΓòÉ

When the Update buttons box is checked, the Query editor will automatically
generate push buttons on the query window. The generated buttons let the user
do the following:

o Insert a row
o Delete a row
o Update row information
o Clear row information
o Reset row information

The Update buttons check box is ignored if No bound objects is set.


ΓòÉΓòÉΓòÉ 11.14. Search buttons ΓòÉΓòÉΓòÉ

When the Search buttons box is checked, the Query editor will automatically
generate push buttons on the query window. The generated buttons let the user
do the following:

o Search the database
o Clear row information

The Search buttons check box is ignored if No bound objects or Bound container
are set.


ΓòÉΓòÉΓòÉ 11.15. OK ΓòÉΓòÉΓòÉ

Click OK when the information shown in the window is complete. If the Movement
buttons, Update buttons, or Search buttons boxes are checked, the requested
bound objects are created.


ΓòÉΓòÉΓòÉ 11.16. Cancel ΓòÉΓòÉΓòÉ

Click Cancel to throw away all changes made to the query object.


ΓòÉΓòÉΓòÉ 11.17. Options ΓòÉΓòÉΓòÉ

Click the Options button to configure the Query editor.


ΓòÉΓòÉΓòÉ 12. Sort ΓòÉΓòÉΓòÉ

Use this page to specify how the rows in the result table should be ordered.
You can sort the table according to a given column (or columns). Columns in the
Column sort order list are included in the ORDER BY clause of the SQL SELECT
statement.

Help for tasks:

   Adding a column to the sort order
   Removing a column from the sort order

Window objects:

   Column sort order
   Remove
   Available columns
   Add
   Add descending


ΓòÉΓòÉΓòÉ 12.1. Adding a column to the sort order ΓòÉΓòÉΓòÉ

You can add a column to the sort order by doing any of the following:

o Click on a column in the Available columns list, then click the Add or Add
  descending button.

o Double click on the column you want to add. By default, an ascending order is
  assumed.

o Drag the column you want to add and drop it on the Column sort order list. By
  default, an ascending order is assumed.


ΓòÉΓòÉΓòÉ 12.2. Removing a column from the sort order ΓòÉΓòÉΓòÉ

You can remove a table from the Column sort order list by doing any of the
following:

o Click on a column in the Column sort order list, then click the Remove
  button.

o Drag the table you want to remove from the Column sort order list, then drop
  it on the Available columns list.


ΓòÉΓòÉΓòÉ 12.3. Column sort order list ΓòÉΓòÉΓòÉ

The Column sort order list contains the names of the columns that will be used
to sort the final result table. Sorting will be done on the first column in the
list, then by the second column in the list, and so on.

The arrow to the left of the column name shows the sort direction-- up means
ascending, while down means descending. By default, sorting is done to make the
result table ascending, but you can change the sort direction to descending by
double clicking on a column name.

You can reorder columns in the list by dragging and dropping them.


ΓòÉΓòÉΓòÉ 12.4. Available columns ΓòÉΓòÉΓòÉ

The Available columns list shows the tables and columns from which you can
choose. To see a table's columns, click on the [+] symbol beside the table
name. Double click on a column name to add it to the Column sort order list.
Double click on a table name to add all columns in that table to the Column
sort order list. You can also drag table and column names to the Column sort
order list.

Note:  You can sort the result table on columns in any selected table. It is
not necessary to select the sort column or columns on the Columns page of the
SQL editor.


ΓòÉΓòÉΓòÉ 12.5. Add button ΓòÉΓòÉΓòÉ

Click the Add button to add the highlighted columns in the Available columns
list to the Column sort order list. The column name will appear in the Column
sort order list with an up arrow beside it, indicating that the column will be
sorted ascending.


ΓòÉΓòÉΓòÉ 12.6. Add descending button ΓòÉΓòÉΓòÉ

Click the Add descending button to add the highlighted columns in the Available
columns list to the Column sort order list. The column name will appear in the
Column sort order list with an down arrow beside it, indicating that the column
will be sorted descending.


ΓòÉΓòÉΓòÉ 12.7. Remove button ΓòÉΓòÉΓòÉ

Click the Remove button to remove the highlighted columns in the Column sort
order list.


ΓòÉΓòÉΓòÉ 13. Test page ΓòÉΓòÉΓòÉ

Use this page to test out the query you have designed. The SQL corresponding to
the selections you have made on the other pages is shown in the SQL statement
field. The results of the query are shown in the Result table when you press
the Test button.

Window objects:

   SQL statement
   Result table
   Test


ΓòÉΓòÉΓòÉ 13.1. SQL statement ΓòÉΓòÉΓòÉ

The SQL statement shows the SQL query based on the selections you have made on
the other notebook pages. You can edit this statement by clicking on it then
typing. If you do modify the SQL statement in this way, your changes will be
reflected on the other notebook pages if you turn back to them.

Some parts of the SELECT statement may appear on separate lines. This is only
for display purposes; carriage returns are removed from the actual SQL
statement that is stored in the query object.


ΓòÉΓòÉΓòÉ 13.2. Result table ΓòÉΓòÉΓòÉ

The final SQL result table is shown here when you press the Test button. While
you cannot modify the Result table directly, you can turn back to any of the
notebook pages to make refinements, after which you can turn back to the SQL
page to test your query again.


ΓòÉΓòÉΓòÉ 13.3. Test button ΓòÉΓòÉΓòÉ

Press the Test button to display the result table based on the query shown in
the SQL statement field.

If the query references a REXX variable, the SQL editor will prompt you for the
variable's value before executing the SQL statement.


ΓòÉΓòÉΓòÉ 14. Tables ΓòÉΓòÉΓòÉ

Use this page to select the tables you want to use in the SQL query. The
Available tables list shows all tables in the database. The Selected tables
shows the tables that are currently selected. Tables in the Selected tables are
listed in the FROM clause of the SQL SELECT statement.

Help for tasks:

   Adding a table to the query
   Removing a table from the query

Window objects:

   Selected tables
   Remove
   Table pattern
   Table type
   Available tables
   Add
   Refresh list


ΓòÉΓòÉΓòÉ 14.1. Adding a table to the query ΓòÉΓòÉΓòÉ

You can add a table to list of selected tables by doing any of the following:

o Click on a table in the Available tables list, then click the Add.

o Double click on the table you want to add.

o Drag the table you want to add and drop it on the Selected tables.

If there are no tables in the Available tables list, you may have the Fill
table list on startup option turned off. Try pressing the Refresh list to fill
the table list. Note that the table list is restricted by the Table pattern
field and the Table type selection.


ΓòÉΓòÉΓòÉ 14.2. Removing a table from the query ΓòÉΓòÉΓòÉ

You can remove a table from the Selected tables list by doing any of the
following:

o Click on a table in the Selected tables list, then click the Remove button.

o Drag the table you want to remove from the Selected tables list, and drop it
  on the Available tables list.


ΓòÉΓòÉΓòÉ 14.3. Selected tables ΓòÉΓòÉΓòÉ

The Selected tables list shows the tables that will be part of the SQL query.


ΓòÉΓòÉΓòÉ 14.4. Available tables ΓòÉΓòÉΓòÉ

The Available tables list shows all tables in the database. You must add a
table to the Selected tables to use it in the SQL query.

Only those tables that match the pattern in the  Table pattern field are
listed.

The Available tables list is initially filled only if the Fill table list on
startup box is set in the Query editor's Query editor options dialog. If the
Fill table list on startup box is not set, either click the Refresh list
button, or press Enter while editing the Table pattern field to refresh the
list.


ΓòÉΓòÉΓòÉ 14.5. Table pattern ΓòÉΓòÉΓòÉ

The Table pattern field describes the types of tables that are listed in the
Available tables list. The form of the pattern is:

tableOwner[.tableName]

where tableOwner is the owner name, and tableName is the name of the table.
Both tableOwner and tableName may contain the SQL wildcard character "%". An
owner or table name ending with a % will match any name starting with the given
prefix. If no tableName is given, all tables for the given owner are listed. If
neither owner nor table name are given, all tables are listed.

Pressing the Enter key while in Table pattern field refills the Available
tables list. Clicking the Refresh list also refills the list.

Some examples of patterns and the tables they match:

%.%
          matches all tables

admin.%
          matches all tables owned by admin.

sw%
          matches all tables whose owners start with sw.

userid.s%
          matches tables owned by userid that also start with s.

%.%ntp%
          matches tables that contain the sequence ntp that are owned by
          anyone.


ΓòÉΓòÉΓòÉ 14.6. Table type ΓòÉΓòÉΓòÉ

The Table type field shows the types of fields that are listed in the Available
tables list. Tables are shown by default, although you can change this setting
to show system tables, views, or system views.

Changing this field automatically refreshes the Available tables list.


ΓòÉΓòÉΓòÉ 14.7. Add button ΓòÉΓòÉΓòÉ

Click the Add button to add the highlighted tables in the Available tables list
to the Selected tables.


ΓòÉΓòÉΓòÉ 14.8. Remove button ΓòÉΓòÉΓòÉ

Click the Remove button to remove the highlighted tables in the Selected
tables.


ΓòÉΓòÉΓòÉ 14.9. Refresh list ΓòÉΓòÉΓòÉ

Click the Refresh list button to refill the Available tables list with tables
that match the pattern in the Table pattern field. Pressing the Enter key while
editing the Table pattern field or changing the Table type setting also
refreshes the list.

If you want the list to be filled automatically when you start the SQL editor,
check the Fill table list on startup box on the Query editor options dialog.


ΓòÉΓòÉΓòÉ 15. Test SQL ΓòÉΓòÉΓòÉ

Use the Test SQL window to test and modify your SQL query.

Window objects:

   SQL statement
   Test
   Results
   OK
   Cancel

Menu items:

   Load
   Save as


ΓòÉΓòÉΓòÉ 15.1. SQL statement ΓòÉΓòÉΓòÉ

The SQL statement field shows the SQL SELECT statement that is stored in the
query object. You can change the statement by typing in this field. You are
free to split the statement over a number of lines; the Query editor will
remove any embedded carriage return and line feed characters before trying to
execute the statement.


ΓòÉΓòÉΓòÉ 15.2. Test ΓòÉΓòÉΓòÉ

Click the Test button to execute the SQL statement. The results will be
displayed in the Results table. If the SQL statement refers to any REXX
variables, you will be prompted to give values for them.


ΓòÉΓòÉΓòÉ 15.3. Results ΓòÉΓòÉΓòÉ

The Results table displays the results of executing the SQL statement. The
table is updated only when you click the Test button.

The Results table is read only.


ΓòÉΓòÉΓòÉ 15.4. OK ΓòÉΓòÉΓòÉ

Click the OK button to close the window and keep the SQL statement.


ΓòÉΓòÉΓòÉ 15.5. Cancel ΓòÉΓòÉΓòÉ

Click the Cancel button to close the window and throw away any changes you have
made to the SQL statement in this window.


ΓòÉΓòÉΓòÉ 15.6. Load ΓòÉΓòÉΓòÉ

Choose the Load menu item to replace the contents of the SQL statement field
with the contents of a file.


ΓòÉΓòÉΓòÉ 15.7. Save as ΓòÉΓòÉΓòÉ

Choose the Save as menu item to copy the contents of the SQL statement field to
a file.