Selection Criteria

Selection criteria determines which items are to be processed by AQL and is designated by the with or if modifiers. Without any selection clauses in an AQL sentence, every item in the file is eligible for processing.

Compound selection clauses are formed with relational operators. When two clauses are connected by an and, both conditions must evaluate to true for an item to be selected for processing. When two selection clauses are connected with an or, or no relational operator is specified between selection clauses, either condition may evaluate to true for the item to be selected for processing.

The value specified in the valuestring clause must match exactly with the stored value for selection to occur. For example:

list entity with city = "irv" city

Only those items that contain irv in the city attribute are eligible for processing.

Selection criteria limits the data that must be met. Multiple criteria may be established to limit data selection to meeting a certain set of limitations. These sets are established by the logical relational connectives.

An active list can be used by another select sentence to narrow down the resulting list. A list can be passed to another list until a save-list or any other AQL, TCL, or FlashBASIC command is invoked. For example:

select entity with state = "ny"

n items selected.

select entity with city = "syracuse"

m items selected.

list entity name city state phone

The above series of sentences first selects all entity items in the state of New York (ny), then out of these selects all entity items in the city of Syracuse, and then outputs a columnar report of name city state and phone.

Relational Operators and Logical Clauses

Relational operators are modifiers used to establish criteria based on the relationship of data to fixed values or other data. For example, relational operators can be used to select a range of zip code values within specified upper and lower limits. This can be done using gt and lt as well as the and relational operator to establish the selection criteria.

Operator	Description
# or ne	Tests for a not equal condition.
< or lt or before	Tests for a less than condition.
<= or le	Tests for a less than or equal to condition.
= or eq or equal	Tests for an equal condition.
> or gt or after	Tests for a greater than condition.
>= or ge	Tests for a greater than or equal to condition.
not or no	Tests a negative condition, reversing a true relation.
# or no or not	Not equal to
< or lt or before	Less than
<= or le	Less than or equal to

Example(s)

sort entity with state eq "ca"

list invoices with amount.due not "0"

list employees with birthday before "1/2/62"

list employees with age gt "21"

select entity with no zip

sort invoices with every amount > "0"

Logical clauses are used in the selection criteria process.

Clause	Description
or or !	Used between two or more conditions to indicate one or the other must be true.
and or &	Used between two or more conditions to indicate that all must be true.

This example shows two selection clauses connected with an and modifier, which means that both conditions must be evaluated as true for items to be selected for processing.

list entity with name "mi]" and with phone "804]"

When applying multiple selection criteria clauses against a single attribute value, it is not necessary to build a separate selection criteria phase for each, as in the examples below:

list customers with state = "ca" or with state = "az" or with state = "va"

This can also be stated as:

list customers with state "ca""az""va"

String Searching

AQL permits the use of string searching (sometimes called wildcards), that allows introducing variables into the selection criteria. Consider the sentence:

list entity with city = "irv]" city

The right-bracket specifies that any character, or characters, may follow the literal string, irv. This means that both irvine and irving are selected. Consider the sentence:

list entity with name "[inc."

Any item whose name attribute ended with inc. is eligible for processing.

The bracket (string searching) characters may also state an including or containing condition, as in the form:

list entity with name = "[rain]"

This includes in the report any item whose name attribute contains the string rain. This includes Raining, All Terrain Vehicles, and The First Rain.

Example(s)

In this selection criteria, only those items whose name field begins with ar are selected.

list entity with name "ar]" name

This example shows two selection clauses connected with an and, which means that both conditions must be evaluated as true in order for the items to be selected for processing.

list entity with name "mi]" and with phone "804]"

This example shows a mutually exclusive set of selection criteria connected with an or. Either condition evaluating to true accepts the item for processing. That is, if the name attribute begins with ar, or the city attribute begins with irv, the item is selected for output.

list entity with name "re]" or with city "irv]" name city

There is virtually no limit to compound selection criteria in an AQL sentence, with the exception of the fact that AQL limits a sentence to a maximum of 9 and clauses.

list entity with name "ar]" and with city "irv]" or with city "san]" and with contact "[joe]"

Wildcard characters may be used to select item-IDs and attributes based on common characters. Wildcards can be used in selection criteria or complex item-lists as follows:

[	Matches characters following the bracket. Ignores characters to the left of the bracket.
]	Matches characters from the beginning of the string to the bracket. Ignores the characters to the right of the bracket.
^	Matches any character in the position occupied by the caret.

Example(s)

Retrieves any item containing at least five characters, the first two beginning with sm, followed by any character, and ending with th. This retrieves smith as well as smyth.

select invoices with name = "[sm^th]"

Lists entity file items whose name attribute contains a string beginning with i, followed by any character, and ending with m.

list entity with name = "i^m"

The example below lists entity items whose name attribute searches for values containing a string beginning with a and followed by two characters. Any two characters following the a meet the selection criteria:

list entity with name = "a^^"

Pre-Processor

The AQL pre-processor is an enhancement to the Access query language used on D3. The pre-processor provides two major functions:

Adds the ability to use and define phrases that contain parts of AQL sentences.
Eliminates the requirement that explicit values be enclosed in quotation marks, except to resolve ambiguities.

The AQL pre-processor is enabled by changing the command definition in the master dictionary for any AQL commands pre-processing is desired. The pre-processor scans the input sentence, expands phrases and determines which words are values, then passes the sentence to the standard AQL compiler. In normal operation, the AQL pre-processor processes the input statement before the AQL compiler.

Quotation Marks

With the AQL pre-processor, quotation marks are not required to distinguish values from other parts of an AQL sentence. Any word that is not in the file dictionary or account master dictionary, is assumed to be a value, provided that it makes sense to use a value in that part of the sentence. If it does not make sense, then an error message is printed and the sentence is aborted.

For referencing item-IDs, the item-ID string should be enclosed in single quotation marks:

list customers '100''101''102'

Spaces between item-IDs are optional. Both item-ID strings and value strings may be included in an AQL sentence:

list customers '100''101''102' with contact = "[barney]"

The example above shows when D3 enforces the rules about single quotation marks surrounding item-IDs and double quotation marks surrounding value strings.

The backslash character may also be used as a string delimiter. This is useful when the string being searched for contains both single and double quotation marks.

In D3, certain exceptions to the rules are allowed. For example:

list entity 88971677 99171776

The single quotation marks that are normally required around the specific item-IDs are no longer required, provided that there are no ambiguities in the AQL sentence. AQL attempts to locate the unresolved items in the AQL sentence as specific item-IDs in the target file, if they could not be resolved as attribute-defining items.

This new set of rules simplifies the syntactical requirements for AQL sentences, yet introduces new variables into the interpretation of AQL sentences. For example:

list md 'description'

This requests the specific item-ID description from the master dictionary of the current account, and only shows that item. By contrast, the example below displays the description attribute for every item in the current master dictionary, since description happens to be a valid attribute-defining item:

list md description

Headings, footings, break-on options, and grand-total headings must still be enclosed in quotation marks.

Example(s)

The following examples show typical AQL sentences in which quotation marks are not required. Each of these examples utilizes the y option, that displays a message defining how the pre-processor interprets the sentence.

sort sales by date with date >= 1/1/04 and <= 12/31/04 (y

[2393] Assumed '1/1/04' is a value used as selection criteria on attribute 'DATE'.

[2393] Assumed '12/31/04' is a value used as selection criteria on attribute 'DATE'

sort sales by date with date >= "1/1/04" and <= "12/31/04"

In this example the AQL pre-processor identified one selection clause with two values and then showed the AQL sentence complete with quotation marks.

list orders 1234 1235 (y

[2391] Assumed '1234' is an item-ID, or modifies the item-ID.

[2391] Assumed '1235' is an item-ID, or modifies the item-ID.

list orders '1234' '1235'

In this example the pre-processor correctly identified two item-IDs and then showed the AQL sentence including quotation marks.

sort g/l by-exp balance > 1000.00 (y

[2395] '1000.00' is a value used to modify the sort clause on attribute 'BALANCE'.

sort g/l by-exp balance > "1000.00"

This example shows that the pre-processor is able to identify that 1000.00 is a value used to modify the by-exp sort clause.

list g/l balance > 1000.00 (y

[2392] '1000.00' is a print limiter that modifies 'BALANCE'.

list g/l balance > "1000.00"

This example shows the AQL pre-processor identify that the value 1000.00 is a print limiter modifying the output attribute BALANCE. The sentence lists all items but only values greater than 1000.00 are printed for the column BALANCE.

NOTE	BALANCE is an output attribute. If the word with is included in this sentence, then BALANCE > 1000.00 becomes a selection criterion.

Example(s)

list invoices

list dict invoices

list only dict invoices

sort dict invoices by a2 a1 a2

sort dict invoices with a1 "a]" s]" by ac

list-item md = 'list-]'

sort-item md = 'list-]' heading "'lc' List items from 'fl'" (p

Index utilization in Selection Criteria

Indexes can be used with the following types of selection criteria:

Selection Criteria

Example

Simple selection criteria

Indexes are utilized with simple selection criteria statements. For example:

select filename with attr "value"

Indexes are used when multiple values are specified, as long as they are part of the same selection clause. For example:

select filename with attr "value1""value2"

Not equal to (#) boolean operator

Indexes are utilized in statements containing the not equal to (#) boolean operator. For example:

select filename with attr # "value"

Indexes are not used when the selection criteria contains multiple boolean # operators.

Wildcards

Indexes are utilized in statements containing wildcards. For example:

select filename with attr "value]"

Indexes are not used when multiple wildcards are specified, unless each wildcard is in its own and selection clause. For example:

select filename with attr1 "value]" and with attr2 "value]"

NOTE—The ^ wildcard cannot be used in select statements against an indexed attribute. Doing so can cause undefined results.

and operator

Indexes can be used when multiple and operators are specified, so long as one of the attribute definitions in the selection criteria contains an index. For example:

select filename with attr1 "value1" and with attr2 "value2" and with attr3 "value3"

If either attr1, attr2, or attr3 contains an index, indexes are utilized.

Logical operators

Indexes are used in statements containing logical operators, so long as all of the above requirements are also met.

Certain caveats apply to the use of indexes in Access:

Indexes are not utilized if an or clause is present in the statement.
Indexes are not utilized if an Access statement is executed while a select list is active.
Rules for attribute 0 are the same as other attributes, as long as the A0 selection criteria is presented in the form:

select filename with a0 "value"

Indexes are not utilized if this form is presented:

select filename = "value"

As of D3 9.0, all comparisons except those using the equals operator (eq or =), use the index. When multiple indexes are available for use in your selection criteria, indexes are processed in the order in which they appear in the query. As such, to decrease query processing time it is highly recommended that queries are constructed with an index ordering that starts with the index that returns the fewest selections. For example, assuming MYFILE has an index for both DATE and TYPE:

SELECT MYFILE IF DATE = "3 MAY 2012" AND IF TYPE NE"REFUND"

will run much faster than:

SELECT MYFILE IF TYPE NE "REFUND" AND IF DATE = "3 MAY 2012"

Since few items are type REFUND, and the comparison uses the NE operator, the second query will return a very long list of Item-IDs to process. Conversely, far fewer items would have been processed on a particular date. Therefore, placing the DATE selection first would make use of the DATE index, generating a very small list of Item-IDs to process.