═══ 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 . ═══ 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.