Querying Information

Querying is the process of looking to see what information is already entered in the Banner database. You can use inquiry forms, query forms, and most application forms to perform queries.

Some forms automatically open in query mode, usually because a large number of records would have to be retrieved. When a form opens in query mode, Enter Query appears in the status line. You can immediately specify search criteria to narrow the search. If a form does not open in query mode you must put the form into query mode before you can enter search criteria.

 

Query  Functions

You can use several functions to perform queries:

Enter Puts the form into query mode so you can enter search criteria to see what information is already in the database. Enter Query appears in the status line.
Execute Searches the database and displays the first set of records that match your search criteria. This function is enabled only if you are in query mode.
Last Criteria Enters the criteria from your last search. You can change any of the criteria. This function is enabled only if you are in query mode.
Exit or Cancel Cancels the query and takes the form out of query mode.
Count Query Hits Counts the number of records that match the search criteria and displays that number in the auto hint.
Fetch Next Set Replaces the current set of displayed records with the next set of records if more records match the search criteria than fit in the window. This function is enabled only if you are in query mode.

 

To query information:

Access the form you want to query.

If the form opens in query mode (Enter Query is in the status line), go directly to step 3.

If the form does not open in query mode (Enter Query is not in the status line), select the Enter Query function.

Enter the search criteria.

You can query information in any field that you can access. You can use the Oracle wildcards % and _. Capitalization matters. Data is matched against the search criteria exactly as you enter them.

Select the Execute Query function. The form displays all records that match the search criteria.

 

To change your search criteria and re-query:

Use these steps after you query information and you want to re-query with different search criteria.

With the original matches displayed, select the Enter Query function. Matching records from the previous query disappear.

  • Select the Enter Query function a second time. The original search criteria appear.
  • Revise your search criteria.
  • Select the Execute Query function. The form displays all records that match the revised search criteria.

To query a checked check box:

  • Select the Enter Query function.
  • Select (check) the check box.
  • (Optional) Enter additional search criteria.
  • Select the Execute Query function. The form displays records that have matching information, including a checked check box.

To count queries:

Use these steps to determine how many records match your query criteria before you execute the query.

Access the form you want to query.

  • If the form opens in query mode (Enter Query is in the status line), go directly to step 3.

If the form does not open in query mode (Enter Query is not in the status line), select the Enter Query function.

  • Enter the search criteria.

You can query information in any field that you can access. You can use the Oracle wildcards % and _. Capitalization matters. Data is matched against the search criteria exactly as you enter them.

  • Select the Count Query Hits function. The auto hint shows the number of matches.

 

Wildcards

Note about using wildcards goes here.  You can use the Oracle wildcards % and _ in the search criteria:

The character % represents any number of unspecified characters.

The character _ represents one occurrence of an unspecified character.

The following examples illustrate the use of wildcards:

To get these results: Enter this:
All entries that contain ma %ma%
All entries that begin with ma ma%
All entries that end with ma %ma
All entries that have m as second character _m%

With experience, it becomes easier to narrow your queries to get the results you want.

 

Extended Queries

With extended queries, you can use SQL*Plus operators to enter more specific query criteria.

To perform an extended query:

Access the form you want to query.

If the form opens in query mode (Enter Query is in the status line), go directly to step 3.

If the form does not open in query mode (Enter Query is not in the status line), select the Enter Query function.

Move to the field where you want to use extended query.

Enter a colon followed by an identifying character such as A. If you are using extended query in more than one field, enter a different character in each field (for example, :B, :C, and :D).

(Optional) Enter additional search criteria.

Select the Execute Query function. The Query/Where window opens.

Enter the search criteria beginning with a colon, followed by the identifying character, the appropriate SQL*Plus query operators, and values to match.

Example: A>’15-SEP-02′

 

 

Click the OK button. The form reappears with the results of the query.

 

Date Queries

Date queries require special care.

Centuries in the Year

You must specify all four digits for a year when you query on a date field. For example, enter 26-JUN-2002, not 26-JUN-02. If you omit the century, zeros are entered for the century. In this example, if you enter 26-JUN-02, Banner expands the date to 26-JUNE-0002, not 26-JUN-2002.

Dates and Time Stamps

Banner stores most dates with a time stamp. You don’t see the time stamp, but it’s part of the date record and can affect the results of a query. When you include a date in your query criteria, Banner often adds the time 00:00:00 to the query criteria. You probably won’t get any matches with this timestamp, resulting in the message Query caused no records to be retrieved. Re-enter.

To get the results you want, use one of these methods to enter the search criteria:

  • Use a relational operator with an earlier or later date. For example, to find records dated 26-JUN-2002, enter >25-JUN-2002 and < 27-JUN-2002. The query results will include all records dated after June 25 and before June 27.
  • Use an extended query. In the Query/Where window, use a two-digit year (for example, 02 rather than 2002). For more information on extended query, see “Extended Queries” on page xxx.
  • Use the TRUNC function. The query :A>’15-SEP-02′ will return all rows that have an activity date (or some other date) of 15 September 2002. However, to find a row(s) that occurs on one date, you need to use the TRUNC function as follows:

TRUNC(:A) = ’05-MAY-2004′