Was this helpful?
Search Qualifications
Qualification is the process of specifying which rows in the table(s) you want to retrieve. You indicate which rows to retrieve by entering your qualification criteria, or search conditions, in the fields on the Retrieve form. This restricts retrieval to those records (table rows) that match or meet the qualification criteria you entered.
For example, if the Retrieve form has a Lastname field, and you enter Lincoln in that field, the word Lincoln becomes a search condition. QBF retrieves only those records with a value of Lincoln in the Lastname field.
Leaving all the fields on the QBF Retrieve form blank is equivalent to specifying no restrictions on retrieval so that all records of data from the table(s) are displayed.
The following figure gives an example of search conditions that restrict retrieval of records from the Managers table to those data rows with the Project of Advertise.
The following figure shows the results of a query that restricted retrieval of records to data rows with the Projects Advertise and TextProc.
If the query target contains too many columns to fit in your window, tab through the columns to scroll to those that are outside the displayed window.
The search conditions you enter in a field can exceed the field's window width on the form. When you enter a long specification in a field, the window scrolls as you type.
You can duplicate a qualification value from a simple field on a form. For information about using auto‑duplication on your terminal, see the appendix "Defining Function and Control Keys."
Trailing blanks are ignored in a qualification specification.
Use various operators and expressions to further qualify your retrieval search.
How Comparison Operators Are Used
A comparison operator is a symbol that informs QBF that you want to compare two values or search for a range of data.
Qualify your query with these comparison operators.
Operator
Description
=
equal to
!=
not equal to
<
less than
<=
less than or equal to
>
greater than
>=
greater than or equal to
For example, the following figure shows a query that searches the Projects table for records with a budget greater than or equal to $12,000.
All comparison operators have equal precedence.
QBF assumes that fields containing values with no comparison operator have a defacto equals sign as the comparison operator. The equals sign works differently when querying non-nullable and nullable fields. For more information, see Queries in Blank and Nullable Fields.
Comparison operators are often combined with the AND operator within a field. For example, entering a search condition of >100 and <900 searches for all values between 100 and 900. You can also use the OR operator. For example, <100 or >900 searches for all values that are either less than 100 or greater than 900. Likewise, >=a <=d retrieves all character strings that begin with the letters a, b, c, or d.
When using a comparison operator on a character field, the search is case sensitive because QBF treats lowercase and uppercase characters as different characters. All uppercase letters come before all lowercase letters. If you enter >a, you cannot find any character strings that begin with uppercase letters, while if you enter >A <a, you can find all strings that began with uppercase letters but no strings that began with lowercase letters.
Just as capital letters are ordered ahead of lowercase letters, QBF orders numbers in a character field ahead of both uppercase and lowercase letters.
You cannot combine a greater than (>) or less than (<) comparison operator with pattern matching. For example, >Sa% is not allowed. For more information on pattern-matching, see Pattern-Matching Characters.
How Logical Operators Are Used
QBF allows you to use the logical operators AND (conjunction) and OR (disjunctive) to qualify retrievals. AND and OR are known as Boolean operators:
When you use the AND operator, QBF retrieves only data records that meet both or all of the criteria you specify.
When you use the OR operator, QBF retrieves data records that meet any one of the criteria you specify.
There are two ways to use the AND and OR operators:
Within a column
Between different columns
For more information on the use of Boolean operators, see the chapter "VIFRED Field Specifications."
Logical Operators Within a Column
You can use logical operators within a data column. For example, if you enter Lincoln or Douglas or Tubman in a Lastname field, the query returns records with a value of either Lincoln or Douglas or Tubman in the Lastname column.
You can explicitly enter the AND operator in a field, as you do the OR operator, or you can imply the AND operator by leaving a space between words. For example, entering Delta Gamma is the same as entering Delta and Gamma.
If you want to search for a string of words separated by literal spaces, you must enclose the string with double quotation marks (" "), because QBF otherwise interprets the spaces as implied AND operators.
For most practical purposes, the AND operator is only useful in numeric fields because the AND operator requires that the data in the field meet two separate and exclusive criteria. For example, a number can be both greater than 10 and less than 100.
Because the AND operator is exclusive, it usually returns nothing in a character column. For example, entering 1776 and Adams and Street in an Address field does not return the address 1776 Adams Street because QBF looks for a single field with a value of 1776 and nothing else, a value of Adams and nothing else, and a value of Street and nothing else. Because no field could meet such impossible conditions, no data records can be found.
You can use the AND operator in a character field if you use the greater than (>) and less than (<) comparison operators. For example, entering the search condition >a and <d retrieves all character strings that begin with either b or c.
Logical Operators Between Columns
When using logical operators between columns, you do not enter the words AND or OR in the form as you do when you use these operators in a single column. In QBF, the AND and OR operators are implied by the way the qualifying criteria is entered in the fields. Simple fields and table fields differ in how logical operators are implied:
On a form with simple fields, the AND operator is always implied when information is entered in more than one field.
On a form in table‑field format, the AND operator is implied when information is entered in the columns across one row of a table field.
In the following figure, for example, QBF only retrieves data from the Project and Managers tables that have the Project ID Advertise AND a Budget of more than $5,000.
The OR operator can be implied only in table‑field format. Values in different rows of a table field are implicitly OR. In the following figure, for example, QBF can retrieve data that has the Project ID Advertise OR has a budget of more than $5,000.
Parentheses--Group Values
When using logical operators, you can use parentheses to group values. For example, to retrieve all projects with budgets less than $5,000 or between $15,000 and $20,000, enter the following expression in the Budget field:
<5000 or (>=15000 and <=20000)
For information on handling queries containing literal parentheses, see Character String Qualifications.
Pattern-Matching Characters
For further qualification of queries, you can perform pattern matching with wildcard characters. wildcard characters are symbols that represent unspecified character values.
In QBF, the asterisk (*) and the question mark (?) are the default wildcard characters. Pattern-matching characters cannot be used in fields with numeric data types.
As explained in The % and _ Pattern Matching Characters, you can specify that your system use the % and _ character in place of ? and *.
? and * Pattern-Matching Characters
Use the question mark (?) to represent one unspecified character. For example, T?P can find the values TAP, TCP, TOP, and so on. You can use more than one question mark to indicate more than one character. For example, to search for all 5‑digit identification codes beginning with 941 enter 941??.
Use the asterisk to represent any number of unspecified characters or no characters at all. For example, to search for all product codes containing the characters ALPHA enter *ALPHA*. This returns answers such as:
122‑STAR‑ALPHA
STAR‑ALPHA‑X4
ALPHA‑987‑PROTO
ALPHA
To search for all product codes that begin with the characters STAR you enter STAR*. This returns answers such as:
STAR‑98‑BETA
STAR‑ALPHA
STAR
You can combine the question mark and the asterisk. For example, to locate any product that begins with four characters and a dash and ends with ALPHA, enter ????‑*ALPHA. This returns answers such as:
STAR‑986‑ALPHA
PROT‑BETA/ALPHA
TEST‑ALPHA
Pattern-matching characters and logical operators interact with each other. For example, you can combine the OR operator and pattern matching characters in a column by entering STAR* or ALPHA‑????. This returns answers such as:
ALPHA‑98G5
ALPHA‑TEST
ALPHA‑5005
STAR‑87‑BETA
STAR‑876‑ALPHA‑X
STAR
For most practical purposes, the AND operator is only useful in numeric fields because it requires that the data in the field meet two separate criteria. Therefore, the AND operator is rarely used in conjunction with pattern-matching characters.
Because QBF treats spaces in search qualifications as an implied AND operator, you must enclose pattern-matching qualifications containing a literal space in quotation marks. For example, in an address field the specification 1776 Adams* returns nothing and does not find 1776 Adams St because the space is treated as an AND operator. To include a space in a character field specification you must enclose the specification in quotation marks. For example, entering 1776 Adams* returns answers such as:
1776 Adams Ln.
1776 Adams St.
1776 Adams Street
1776 Adamstown Court
To use the asterisk or the question mark as actual characters rather than as wildcard characters, precede them with the backslash character (\). For example, enter A\*B to look for the actual sequence A*B. If you enter A*B by mistake, QBF searches for any combination of any number of characters that begin with the letter A and end with the letter B.
You cannot use a wildcard pattern-matching symbol in conjunction with a greater than (>) or less than (<) comparison operator. For example, >Sm* is not allowed.
% and _ Pattern-Matching Characters
You can choose to use the underscore (_) and percent sign (%) pattern-matching characters in place of the ? and * characters. The underscore is equivalent to the question mark and the percent sign is equivalent to the asterisk. To specify these pattern-matching characters, use the II_PATTERN_MATCH environment variable/logical as explained in the System Administrator Guide.
Bracketed Expressions
By enclosing characters within brackets ([ ]), you can stipulate specific values in a pattern-matching search. For example, if you want to find employees whose last names begin with R or T, enter [RT]* in a Lastname field. This returns last names such as Randall, Rotelli, Tamatomi, and Tijerina. You can include any number of characters within brackets and they can be placed in any order.
All pattern-matching queries work in Query mode. However, if you invoke QBF with the ‑e flag (for expert mode) you cannot use bracketed pattern-matching operators for specifying selection criteria in an empty catalog in order to retrieve a set of tables or other objects for querying.
A bracketed pattern is often used when a table contains data in different cases. For example, if you entered S* as a query specification in the Dept field, a retrieve returns rows with Sales, but not rows with sales. However, the query specification [Ss]* returns all rows containing both Sales and sales. By separating characters with a hyphen and surrounding them with brackets, you can stipulate a range of characters for pattern matching. Thus, entering [A-M]* in the Name field retrieves the names of all employees whose last names begin with any letter in the first half of the alphabet, such as Alcott, Chung, Feldmann, King, and Moore. The wildcard characters can be combined with bracketed expressions. Used either before or after the brackets, they further refine a search for patterns in data.
Complex Queries
The following figure is an example of a complex query that utilizes comparison operators, logical operators, and wildcard characters. The query searches for information from a JoinDef consisting of the Staff table and the Tasks table. Specifically, it searches for records of employees whose names begin with the letter B, whose hourly rate is less than $50, and who have either worked in the Design phase or have worked more than 25 hours in the Implement phase.
QBF interprets the search conditions on this form as follows:
Display the data if name = B* and hourly rate <50 and
(task = Design or (task = Implement and hours >25))
Character String Qualifications
You can use case, parentheses, and multiple words in character strings.
Case — Character string search conditions are case-sensitive. For example, if you enter Franklin as a search condition, QBF displays only records with Franklin, but not FRANKLIN or franklin.
Parentheses — If a character string contains a literal parenthesis, you must enclose the entire string in quotation marks. For example, to retrieve the record for Martin (E) Smith, enter Martin (E) Smith.
Multiple Words in Character Fields — If a character field contains two or more words separated by spaces, such as a street address, you must either use a pattern-matching wildcard character, as explained in Using Pattern-Matching Characters, or enclose a multi‑word search condition in quotation marks.
For example, suppose you want to find records with a value of 1776 Adams Street in the Address column. You could do this by entering 1776 Adams Street as a search condition in the Address field.
However, if you enter 1776 Adams Street with no quotation marks as a search condition in the Address field, nothing is found because QBF interprets the spaces between the three words as implied AND operators.
As explained in the section on pattern-matching, you could also use a wildcard character. For example, entering a search condition of 1776* finds all addresses on all streets beginning with 1776. Entering a search condition of 1776 Adams St* finds both 1776 Adams St. and 1776 Adams Street.
Date and Time Qualifications
Date fields can hold either a simple date, such as 22-mar-1998, or a date and time, such as 22-mar-1998 10:44:23. You cannot use wildcard characters in date fields to qualify a retrieval. However, you can use comparison and logical operators to retrieve a range of dates and/or times.
For example, to retrieve all records with dates from January 1, 1998 through December 31, 1998, enter the following qualification in a date only field:
>=1‑jan‑1998 and <1‑jan‑1998
If the field contains only date values and no time values, you can retrieve all records for a single date, such as March 22, 1998, by entering a qualification such as:
22‑mar‑1998
If the field contains values in the date and time format and you want to retrieve only records with the specific date and time of March 22, 1998 10:44:23, enter the qualification:
22‑mar‑1998 10:44:23
If the field contains both the date and time and you want to retrieve all records for March 22, 1998, regardless of the time, enter the following date range qualification in the date and time field:
>=22‑mar‑1998 and <23‑mar‑1998
To qualify the retrieval for a specific hour of one day, enter a qualification such as:
>=22‑mar‑1998 10:00 and <22‑mar‑1998 11:00
Queries in Blank and Nullable Fields
Query specifications differ between nullable and non-nullable fields. For general information on nulls and nullable columns, see the chapters "Using the Tables Utility" and "Working with Data Types and Data Display Formats."
In a nullable column, blank fields are treated as nulls (no data). In a query operation, entering the equals sign (=) into a nullable numeric column without anything else tells QBF to look for and return only rows in which the specified field is null.
In a non-nullable column, blank fields are treated as if they contain a value of zero (0). Entering the equals sign (=) into a non-nullable column without entering anything else returns all rows containing a value of zero (0) in that field.
Last modified date: 01/30/2023