Use Query in form-type maintenance screens to define a result set of the database table whose data is maintained by the function. (It is not available in screens that have only a table window, such as the Fiscal Years screen.) Using the fields in the Selection Criteria group box, indicate the rows of the database table to be made available and the order in which they should be sorted. Once you have "executed" the query and the result set is defined, you can use the Select pushbutton to bring up a highlighted row for immediate review or editing. The rest of the result set is readily available for review or editing by using the Next, Previous, First, and Last options under the Options menu (you can also use the toolbar buttons).
Query is also useful as a quick inquiry tool. Use the selection criteria to define the inquiry, and then review the information in the table window. Move and resize the columns to tailor the inquiry to your requirements.
Once you have defined the result set using the Execute pushbutton, the result set rows are the only rows available (other rows are ignored). To access the entire database table again, use the New button in Query, or select Execute in Query with no selection criteria.
The number of rows placed in the table window are limited by the Maximum Query Rows setting in the System Settings screen in Costpoint System Administration. If more than that number of database rows match the search criteria you specify, you must narrow down your search criteria. If the Maximum Query Rows is set high, the population of the Query table may take some time. To halt the table window population once it has started, use the Stop pushbutton. At that point you can narrow down your search criteria and query again.
The fields of the table window are shown in this drop-down box. Choose the field you wish to use as a selection criterion.
Use this drop-down box to define a relationship between Field and Value. The relations available are:
Is Equal to
Is Greater than
Is Greater than or equal to
Is Less than
Is Less than or equal to
Is Not Equal to
Is Blank
Is Not Blank
Begins With
Ends With
Contains
Value is used with Field and Relation to define the rows to be selected from the database. Enter the value to which you want to limit your search.
For example, to select all accounts less than 02000 in the Maintain Accounts screen, choose:
Field: Account
Relation: Is Less than
Value: 02000
Another example would be to select all active accounts.
Field: Active
Relation: Is Equal to
Value: Y
Yet in another example, to retrieve all accounts beginning with 02, you would enter:
Field: Account
Relation: Begins With
Value: 02
Select this checkbox if you want the search to look for exact upper- and lower-case matches for string fields. If you leave the checkbox unchecked, all occurrences of the search value will be selected, regardless of case.
Use this group box to make more complex selection criteria statements. First, select the Field, Relation, and Value. Then select the Replace pushbutton. You will see your new statement in the Current list box. Then, change Field, Relation, and Value to reflect the second portion of the statement. Select either the And or Or radio button. And should be chosen when both parts of the statement must be true for selection to take place. Or should be chosen when either statement being true will cause a record to be selected.
Use the Add pushbutton to add the new statement to the Current list box.
This box shows the statements as you create them. The highlighted row shows the statement that you will modify if you select the Replace pushbutton or that you will remove if you select the Delete pushbutton.
Use this to create more complex statements. For example, if you want to see all accounts greater than 02000 but less than 05000, you would first create the statement:
Account Is Greater than 02000
Select the And radio button in the Combine Method group box. Then change Relation and Value to create the statement:
Account Is Less than 05000
Select the Add pushbutton. This will give you the following statement:
Account Is Greater than 02000
And Account Is Less than 05000
Once you have selected the desired values in Field, Relation, Value, and the Combine Method group box, use this pushbutton to enter the statement into the Current list box. The statement that you had already highlighted will be modified to reflect the new selection.
Use this pushbutton to remove the highlighted statement from the Current list box.
The columns of the table window are shown in the drop-down box. This field will affect the order in which the rows of the result set are accessed when using the Previous, Next, First, and Last menu options (or the toolbar buttons).
Choose the one field on which the result set should be sorted. Then select either the Ascending or Descending radio button.
Select the Count pushbutton and you will see how many database rows satisfy the current selection criteria.
Select this pushbutton when you are satisfied with your selection criteria. The statements in the Current list box are used to retrieve the defined rows. The rows then appear in the table window.
Select this pushbutton to retrieve a highlighted row into the data entry form and close Query. Double-clicking on the highlighted row has the same effect.
Use this pushbutton to remove all the Current selection criteria and clear the previous result set from memory. To use the Previous, Next, First, and Last options again, you need to execute another selection criteria set.
If many rows are being selected by Query, you can stop the Query process by selecting the Stop pushbutton. This pushbutton gives you control of the data so you don't have to wait long periods of time for Query to finish selecting all rows.
The Stop pushbutton is available for non-Oracle users. This pushbutton is visible only during the actual execution of the Query feature.
For Oracle users, the Stop pushbutton is not visible. Only the visible rows in the table windows are populated before you have control again. As you scroll down the Query table, more rows are selected from the database. See the note for Oracle users below.
Use this pushbutton to close the Query window. If you have executed a query while in the Query window, the form will be cleared, as if the New pushbutton had been selected on the Toolbar.
Features have been added to the Query dialog that provide special behavior on Oracle only. These changes will work only if you make changes to your SQL.INI file.
To invoke the new features, add a line to the ORAGTWY section of your SQL.INI file to set up an "alias" database name that points to the Oracle database you are using. The alias name must be the mirror image of the real database name. In the example below, SQL.INI is set up to access two databases ("O120DRN" and "SUPERTEC"). The after image shows that the database alias names for both databases have been added.
Before
[ORAGTWY]
REMOTEDBNAME=O120DRN,@X:DB3-SPX
REMOTEDBNAME=SUPERTEC,@deltekcp
SUBSTITUTE=SYSSQL
After
[ORAGTWY]
REMOTEDBNAME=O120DRN,@X:DB3-SPX
REMOTEDBNAME=NRD012O,@X:DB3-SPX
REMOTEDBNAME=SUPERTEC,@deltekcp
REMOTEDBNAME=CETREPUS,@deltekcp
SUBSTITUTE=SYSSQL
When you are running Costpoint on Oracle and you have your SQL.INI file set up correctly, the Stop button normally found on the Query dialog will be hidden. When you execute a Query, the program will only fetch enough rows to fill the visible portion of the table window. Then it returns control to you and only fetches additional rows if you move the Query table scroll bar, use the down arrow key to scroll through the Query table, or use the Next menu/toolbar option.