Advanced Query works in the same way as the Simple Query, except that you must compose the query yourself. Unlike the Simple Query, the
field names are not provided in a drop-down menu (if selected for viewing, however, their names can be displayed on the same page so
that you can choose the relevant fields for your query). You can construct more complex tests by joining together tests with AND, OR and NOT, and also by using brackets. The
comparators to be used in Advanced Queries (Boolean) differ in form, but not in meaning, from most of those used in simple queries.
They are:
Advanced Query comparators
Equivalent comparator for Simple Query
LT (or <)
is less than
LE (or <=)
is less than or equal to
EQ (or =)
is equal to
NE (or !=)
is not equal to
GT (or >)
is greater than
GE (or >=)
is greater than or equal to
Missing
Missing [1]
Matches (or ~=)
Matches [2] [3]
Contains (or >>)
Contains [3]
in
not available in Simple Query
The Missing comparator does not require a value to test against. Fields which are missing fail every other test except 'is not
equal to'. It is only true if the field is set to its special missing value(s). A field can have several missing values
designated, with different 'meanings'.
The Matches comparator uses the Extended Regular Expression matching rules (as with Simple Queries), an
explanation of which can be found in the Regular Expressions help page.
The comparators Contains and Matches can only be used with original data
values, and not for translated values of codes.
Examples of advanced queries
size GT 55 AND region = nw
This will match records where the field size contains a numeric value greater than 55 AND where the field region contains the characters 'nw'.
(size GT 55 OR county = herts) AND type =A
This will match all records where either the size field has a value greater than 55 OR the county field contains 'herts', AND where the type field contains the single character 'A'. Placing brackets around queries has the effect of forming a single query, which, in turn, can be joined to other queries with NOT, AND and OR.
size in 10,20,30,40
This will match all records where the size field has values of 10,20,30 or 40
County >> Shire AND County !=Hampshire
This will match all records where County contains 'Shire' except for 'Hampshire'
NOT County missing
This matches all records where County is not missing (has not been set to a value defined as any of the missing value codes).
Help
