To create a more sophisticated search in SQL (Structured Query Language), click the Adv button to the immediate left of the Filter panel; 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 eight different SQL Queries; these are automatically saved between DXKeeper sessions. Notice that this panel also gives you the ability to construct more sophisticated UTC filters, specify BAND and MODE filters, and compose up to four advanced Sorts. Like the SQL Queries, the Sorts are also saved between sessions.
The SQL Query Filters panel contains four query textboxes that display either SQL Queries 1 to 4, or SQL Queries 5 to 8; click the ~ button in the panel's upper right corner switch between the two sets of four. To create a Query, enter the appropriate 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 makes it easy to compose queries and immediately see their results.
The first SQL Query can be directly invoked
from the Main window's Filter panel by clicking the SQL1
button.
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 occuring 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, date constants 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.
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:
| 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') |
| 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.
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
.