Ad hoc Query User's Guide
Welcome to the Ad hoc Query. The Ad hoc Query consists of three main steps. Columns with a "code" value have a corresponding description column and users can specify sort order and column display order.
The Query Engine searches for data elements using the user's table and column selections and other search criteria.
Follow these three basic steps:
STEP 1: View Selection
Ad hoc query information has been organized into views, meaning that data of a specific type are grouped together (e.g., the Submission Information view contains submission-specific information). You may select only one view to query. Click the "View Data" link for the subject of your choice. This selection will automatically bring you to Step 2: Selection of Columns for Ad hoc Query.
STEP 2: Column Selection
You may select one or more columns that are listed according to your view selection. Click the underlined column name to view that column's metadata. To select the columns that will be used for your output, click on the box next to the column name.
If you decide to select columns other than those already chosen, you may either click the box again to deselect, or click the Reset button that appears at the bottom of the page. A Step 3: Enter Search Criteria button appears at the bottom of the page. You must select one or more columns before clicking the Step 3: Enter Search Criteria button, to proceed to the third step. If you click on the button without making a selection, a notice will appear stating that you must return to the column list on the previous page and make at least one column selection. Once you make the column selection, click on the Step 3: Enter Search Criteria button.
Step 3: Output Options Selection
The Query Form for the Ad hoc Query allows you to organize the output data according your selections from Steps 1 and 2. Arranged in a table format, seven fields for output data organization appear: Column Name, Operator Definition, Search Value, Column Display Order, Sort Column, Sort Order, and Where Only.
Under Column Name, each column you selected from Step 2 appears. A brief description appears in the column next to the column name. Click on the underlined column name to retrieve an extended description of that column. The metadata describes the column data type and provides a list of permitted values where applicable.
Operator Definition and Search Value
The Operator Definition and the Search Value fields allow you to enter specific search criteria to further refine the information that is being selected. The Operator Definition field allows you to specify the scope of the search value that you are entering under the Search Value option. It is predicated on the type of column that you selected: character, number or date. For character columns, you are allowed to search for all values containing the value, beginning with or equal to the value. Each of these operator definitions gives you a greater specificity for the search. For number values, you have equal to, greater than/equal to, less than, and less than/equal to. For date values, you have starting from, equal to, and ending with.
You will only be allowed to enter Search Values that are appropriate to the type of the column. For instance, if you entered a character value in a number column, that value will be rejected by the query engine. If you are unsure about column type, clicking on the column name will take you to a page detailing the column.
Column Display Order
The Column Display Order field allows you to change the order of the columns for the output. If you do not insert an order, the columns will remain listed in the order that the columns were selected. To change the order, insert numeric values into the blank fields, beginning with 1 for the first column to be listed, 2 for the second column, and so on. You need enter only the numbers for those columns that you specifically care about ordering. All the remaining columns, you may leave blank and the query engine will reshuffle them automatically. For instance, if you wish the last column listed here to be the third column in the output, simply enter 3 in the last column and leave the others blank.
This option allows you to sort the entire output by a specific column (or columns) data. For instance, if you wanted to order the entire output by the Endpoint Name, (assuming that you selected the All Endpoint Result Information view) you would put a 1 next to the Endpoint Name field, indicating that you first wish the output data to be sorted by the endpoint name.You may use the sort order column to further refine the ordering of the output.
Once you have entered values for the sort column, you may choose to further refine the sorting of data by choosing an option for the Sort Order. You may select to arrange the data in either ascending or descending order. Ascending order will list the smaller data values first and the larger data values last; descending order will list the larger data values first and the smaller data values last. Please note that if you do not enter a value for the sort column, this entry will be ignored.
Once you have entered all of your search criteria and organized your output data, you must click the Search Database button, the Get Results in Excel Format button, or the Reset button. Click the Search Database button to run the query. To save the results to your hard drive click the Get Results in Excel format button. If you select this option, you will be given the file name and you will be asked to save the file to your hard drive. To re-enter the search criteria or to re-organize the output data, select the Reset button.
This option allows you to separate variables into search and retrieval categories. By clicking on a specific variable in this column, you are specifying that the variable should only be used to specify what to retrieve from the database but should not appear as part of the data retrieved and shown to you by this query. Please note that you will therefore have to specify a search value for the variable if you click on the where only column for the variable. Also neither the Column Display Order option or the Sort Column option is valid and will be ignored in the retrieval, if you mark this variable as Where Only, since those options only apply to variables that are going to be displayed.