Performing Query-by-Example and Query Count (User, System Administrator, and Flexfields Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Performing Query-by-Example and Query Count

If you want to retrieve a group of records based on more sophisticated search criteria than what Query Find allows, you can use a feature called query-by-example. As an expert user you can use query-by-example to specify complex search criteria in any of the fields of your current block. The search criteria can include specific values, phrases containing wildcard characters, and/or phrases containing query operators to help you pinpoint the data of interest.

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.

Matching Exact Values

In some cases you want to search for information that matches exact values in one or more fields. For example, suppose you want to search for a budget named FY90 in the Define Budget window of your Oracle General Ledger application. Simply enter the value FY90 in the Name field. You should be precise in your use of uppercase and lowercase, since "fy90" is not always equivalent to "FY90" in query-by-example mode (some fields may not have case-insensitive query).

Finding Patterns With Wildcards

Include wildcard characters in your search criteria to search for particular patterns in your data. You can use the percent '%' wildcard within a field to represent any number of characters (including no characters), or use the underline '_' wildcard to represent any single character. For example, if you want to retrieve all records that contain the word "Central" in a specific field, you would enter the search criterion %Central% in that field.

Using Query Operators

Use query operators in your search criteria to restrict your search to the information you need. The query operators you can use in most fields include:

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)

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.

To use query-by-example:

Enter

This action switches your window from data entry mode to Enter Query mode, as indicated by the Enter Query message that appears in the status line.

Show Last Criteria

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.

Choose

Run

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.

Choose

Cancel

To obtain a query count:

Perform steps

Count Matching Records