To create more sophisticated filters by using SQL (Structured Query Language), click the Adv button located above the Log Page Display on the Main window's Log QSOs tab; this will display the DXKeeper's Advanced Log Sorts and Filter window. Using the SQL Query Filters panel, you can compose and activate up to 16 different SQL Queries (displayed as four banks of four filters); these filters are automatically saved between DXKeeper sessions.
The SQL Query Filters panel contains four query textboxes that display one of four filter banks:
SQL Query Filters 1 to 4
SQL Query Queries 5 to 8
SQL Query Queries 9 to 12
SQL Query Queries 13 to 16
Clicking the ~ button in the panel's upper right corner causes the panel to display the next bank of filters.
To create a Query, enter the appropriate SQL expression in one of the panel's four query textboxes; to use that query as a log filter, click the Filter button to immediate right of the query text box. If you have enough screen space, arranging things so you can see both the Advanced Log Sorts and Filter window and the Main window's Log QSOs tab makes it easy to compose queries and immediately see their results.
SQL Query Filters can also be invoked from
the Filter panel textbox on the Main window's Log QSOs tab
-- but only if you specify a caption in the textbox to the left of the SQL
expression in the SQL Query Filters panel. A bank of SQL Query
filters is considered active if at least one caption is specified.
By clicking the ~ button in the Filter panel textbox, buttons for
each filter in the next active bank of SQL Query filters will appear in the Filter
panel textbox, each bearing its specified caption. Allowing the mouse cursor to
hover over an SQL Filter button in the Filter panel textbox will produce
an explanatory popup showing the SQL expression associated with the button;
starting each SQL expression with a comment describing
its function will make these popups more useful.
The database schema for logs contains one record for each QSO, and each record
contains an identical set of fields. Use each
field's specified ADIF field name when constructing a
query.
At the very top of the Advanced Log Sorts and Filter window, you'll find
a pull-down list containing all valid field names. Having selected a field name
in this list, double-clicking in one of the four query textboxes in the SQL
Query Filters panel will append the field name to the query.
A simple SQL query that shows only your QSOs with VK9NS would be
CALL='VK9NS'
We could have simply used the main window's Call filter to accomplish that
query, but
(CALL='VK9NS') AND (QSO_Begin > #1997-06-01
12:00#)
shows how to incorporate a constraint on the QSO's begin time, in this case
showing only QSOs occurring after noon UTC on June 1, 1997. Notice the use of the
ISO date format, which is YYYY-MM-DD HH:MM:SS
. In SQL, dates must be enclosed between # symbols.
(CALL='VK9NS') AND (QSO_Begin BETWEEN #1997-06-01 12:00# and #1999-12-1#)
illustrates the use of the BETWEEN
operator to find QSOs within a
specified date/time range.
(CALL='VK9NS') OR (CALL='VK9NL')
shows all QSOs with the Smith family, illustrating the use of the OR
operator.
To show only QSOs made in PSK, you could combine a set equal tests, e.g.
(MODE='PSK31') OR (MODE='PSK63') OR (MODE='PSK125') OR (MODE='PSK250')
but an equivalent expression using the IN operator is more readable, as well as less typing:
MODE IN ('PSK31','PSK63','PSK125','PSK250')
If you're searching for an item that contains an apostrophe (single quote), use double quotes to surround the item, e.g.
CNTY="IA,O'BRIEN"
SQL provides the LIKE operator and wildcard characters to enable broader searches by specifying a pattern, for example
CALL LIKE 'VK9*'
which shows all QSOs with callsigns whose first three characters are VK9. The Asterisk wildcard character matches 0 or more characters. The Question Mark wildcard character matches exactly one character. Thus
CALL LIKE 'VK9?'
shows all QSOs VK9X, but not those with VK9NS or VK9NL.
(DXCCPrefix='VK9-N') AND NOT (CALL='VK9NS')
uses the NOT operator to show all Norfolk QSOs not involving Jim.
Besides the Asterisk and Question Mark, the LIKE operator provides wildcard characters that let you specify a single digit, or a range of characters, as illustrated in the table below:
| To match... | Example | Samples that match | Samples that don't match |
| one or more characters | VU4*W | VU4CW, VU4WWW, VU41W | VU2CW, VU4DY |
| one or more characters | *YV1DIG* | YV1DIG, YV0/YV1DIG, YV0/YV1DIG/QRP | YV0/YV1DX |
| one character | OX1VHF/? | OX1VHF/P, OX1VHF/5,OX1VHF/M | OX1VHF, OX1VHF/MM |
| one digit | A6#AJ | A61AJ, A64JA | A6JA, C61AJ |
| a range of characters | A[A-L]6DX | AA6DX, AF6DX | AM6DX, A6DX, AA6DY |
| outside a range of characters | K[!G-H]4DX | KC4DX, KK4DX, K$4DX | KG4DX, KC4DY |
| outside the range of digits | K5[!0-9] | K5K, K5% | K50 |
| a pattern composed of several sub-patterns | A[A-L]#[A-Z]* | AA6YQ, AL7X | AM4DX, KH6/AL7X, AA6 |
| characters that are wildcard characters | [*]Footnote | *Footnote | Footnote, -Footnote |
Note that you can combine multiple wildcards to specify more complex patterns.
In summary, you can use the following comparison and logical operators to create filters:
| Comparison Operator | Meaning | Example |
| = | equal to | PROP_MODE='F2' |
| < | less than | QSO_BEGIN < #2003-12-31 12:00# |
| <= | less than or equal to | QSO_END <= #2003-12-31 12:00# |
| > | greater than | QSO_BEGIN > #2003-12-31 12:00# |
| >= | greater than or equal to | QSO_END >= #2003-12-31 12:00# |
| <> | not equal to | MODE <> 'SSB' |
| LIKE | used to match a pattern | QTH LIKE '*Pittsburgh*' |
| BETWEEN...AND | used to specify a range of values | QSO_BEGIN BETWEEN #2003-12-31 12:00# and #2004-01-01 12:00# |
| IN | used to specify a set of values | MODE IN ('PSK31','PSK63','MFK8','MFSK16') |
| Logical Operator | Meaning | Example |
| AND | both conditions must be true | (PROP_MODE='F2') AND (MODE <> 'SSB') |
| OR | either condition can be true | (QTH LIKE '*Pittsburgh*') OR (QTH LIKE '*Philadelphia*') |
| NOT | logical inversion | (STATE='PA') AND NOT (QTH LIKE '*Philadelphia*') |
If the character string <filtertextbox>
is found in an SQL Query being executed, it is replaced by the contents of the Filter
textbox. Thus the query
QTH like '*<filtertextbox>*'
with the Filter textbox set to
Philadelphia
results in execution of the query
QTH like '*Philadelphia*'
If you then change the contents of the Filter textbox to
Pittsburgh
and invoke the query,
QTH like '*Pittsburgh*'
will be executed.
Any characters between // and // are ignored, and thus can be used as explanatory comments, e.g.
//Find QTH // QTH like '*<filtertextbox>*'
Abbreviations
If the character string USER_X is found in an SQL Query being executed, DXKeeper inspects each user-defined item definition seeking one whose caption is X; it then replaces USER_X with the ADIF name of the user-defined item. Thus if user-defined item #2 has a caption of 10-10, the SQL expression
USER_10-10='123.45'
would be converted to
APP_DXKEEPER_USER_DEFINED_2='123.45'
before execution.
The fields QSLSDATE, QSLRDATE, APP_DXKEEPER_EQSL_QSLSDATE, APP_DXKEEPER_EQSL_QSLRDATE, APP_DXKEEPER_LOTW_QSLSDATE, and APP_DXKEEPER_LOTW_QSLRDATE use the value 4000-01-01 to mean "no date specified". To avoid confusion, appropriately qualify expressions using these dates, for example
(QSL_SENT = 'Y') and (QSLSDATE > #2006-01-01#)
Without the (QSL_SENT
= 'Y') qualifier, (QSLSDATE
> #2006-01-01#)would select all QSOs whose QSL Sent
Date is unspecified.
A online reference for SQL as supported by the Microsoft Jet engine, which is incorporated in both DXKeeper and Microsoft Access, is available at http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html .
A summary of functions that can be used in
SQL expressions is available in http://www.webcheatsheet.com/sql/access_functions/month.php
.