Previous | Next | Contents | Index | Navigation | Glossary | Library |
In addition, if you do not actually need to retrieve the records from a query-by-example search, but only want to know how many records match your search criteria, you can perform a query count to save yourself some time.
Query Operators
Operator | Meaning | Example Expression |
---|---|---|
= | equals | = 'Janet' |
= 107 | ||
!= | is not | != 'Bob' |
!= 109 | ||
> | greater than | > 99.1 |
> '01-JAN-93' | ||
>= | at least | >= 55 |
< | less than | < 1000.00 |
<= | at most | <= 100 |
#BETWEEN | between two values | #BETWEEN 1 AND 1000 |
Table 1 - 37. Query Operators (Page 1 of 1) |
Attention: If your field contains character or date values and you are using operators, you must enclose the character or date value in your expression in single quotes as shown in the above examples.
Attention: You cannot use query operators on time fields.
Attention: When you use the #BETWEEN query operator, the search criterion "#BETWEEN value1 AND value2" retrieves all records containing values between and including value1 and value2. For example, the search criterion "#BETWEEN '01-JAN-93' AND '01-MAR-93'" entered in a date field retrieves all records with dates between and including 01-JAN-93 and 01-MAR-93.
The query operator expressions retrieve results according to character ordering rules for character fields and numeric ordering rules for numeric fields. For example, suppose you have the following field values:
002, 003, 004, 005, 078, 123,
253, 441, 576, 775, 1274, 3298,
4451, 5600, 9578, 10500, 58437, 70845
These values are shown in the order you expect for numeric values, where 005 is between 004 and 078. If the field is defined as a numeric field, then the phrase "#BETWEEN 004 AND 078" would retrieve 004, 005, and 078. However, if the field is a defined as a character field, then the phrase "#BETWEEN 4 AND 7" would retrieve the values 441, 576, 4451, 5600, and 58437, which all start with characters between 4 and 7. The values 775 and 005 would not appear because 775 is lexically greater than 7, and 0 is lexically less than 4.
Some fields contain date values that are actually "Date-format" character values. These fields behave like character fields, such that the value 01-FEB-92 would be lexically less than 01-JAN-92, because F precedes J in a character set.
Sometimes you cannot instantly tell if a field containing numeric values is defined as a character or numeric field. To identify what the field type is, you may have to enter and experiment with different search criteria expressions to see what results get returned.
Attention: In Enter Query mode, all check boxes are in a null state; that is, they are neither checked nor unchecked, although they may appear checked or unchecked as a default. If you want your search criteria to include a checked check box, you have to explicitly check it, regardless of whether it initially appears to be checked. Similarly, if you want your search criteria to include an unchecked check box, you have to explicitly uncheck it, regardless of what it initially appears as.
Attention: To retrieve all the database records for a block, you can bypass 1 and 2 and simply choose Run from the Query menu. This is referred to as a blind query. You can also retrieve all the records for a block by choosing Find All from the Query menu.
Attention: If you simply choose Count Matching Records from the Query menu without specifying any search criteria in a block, query count displays the total number of records in the database for that block.
Previous | Next | Contents | Index | Navigation | Glossary | Library |