DXKeeper Online Help Contents
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.
typed directly into the Filter panel
textbox can be used to immediately filter Log Page Display by striking Ctrl-S.
The database schema for logs contains one record for each QSO, and each record
contains an identical set of fields that store that QSO's items. Use each
field's specified Item Name name when constructing a
In the top-right of the SQL Query Filters panel, 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
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-01#)
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.
Items are representing using one of three data types:
String (a sequence of alphanumeric characters)
Integer (a number without a decimal separator)
Number (with or without a decimal separator)
As shown in the above examples, items whose data type is String must be enclosed by single or double apostrophes, e.g.
Items whose data type is Date must be enclosed by a # symbol, e.g.
QSO_Begin BETWEEN #1997-06-01 12:00# and #1999-12-01#
Items whose data type is Number are not enclosed by symbols, e.g.
(Freq > 7.0) and (Freq < 7.025)
Note that in items of type Number, the period character is used as a decimal separator -- even in locales where a different character is used for this purpose.
Each item's data
type is specified here.
For a new QSO to be logged, it's Call, Band, Mode, Begin, DXCC Prefix, and DXCC Country Code items must all contain valid values. DXKeeper can be configured to automatically set some of a new QSO's items - like Station Callsign or myQTH -- to default values. The remaining items are initialized to an empty value as a function of their data type:
|Data Type||Empty Value|
|String||a string containing no characters|
In the Log Page Display and in the panels on the Main window's Log QSOs tab, an item containing an empty value is not displayed.
Thus in SQL expressions, items of type Date like QSLSDATE, QSLRDATE, APP_DXKEEPER_EQSL_QSLSDATE, APP_DXKEEPER_EQSL_QSLRDATE, APP_DXKEEPER_LOTW_QSLSDATE, and APP_DXKEEPER_LOTW_QSLRDATE must be appropriately qualified; 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.
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:
|<||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')|
|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*')|
QTH like '*<filtertextbox>*'
with the Filter textbox set to
results in execution of the query
QTH like '*Philadelphia*'
If you then change the contents of the Filter textbox to
and invoke the query,
QTH like '*Pittsburgh*'
will be executed.
If the character string <filtertextboxdxccid> is found in an SQL Query being executed, it is replaced by ARRL Country Code of the callsign or callsign fragment specified in the Filter textbox. Thus the query
DXCCID = '<filtertextboxdxccid>'
with the Filter textbox set to
results in execution of the query
DXCCID = '123'
where 123 is the ARRL Country Code for Johnston Island.
is running, then "callsign to country code" lookup will reference any
Any characters between // and // are ignored, and thus can be used as explanatory comments, e.g.
//Find QTH // QTH like '*<filtertextbox>*'
If the character string Deleted_Entity is found in an SQL Query being executed, DXKeeper replaces this character string with an expression that evaluates to TRUE if the QSO's DXCC entity is deleted. Thus the SQL expression
Deleted_Entity AND (BAND='40m')
would filter the Log Page Display to contain only 40m QSOs made with deleted DXCC entities.
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
would be converted to
The following abbreviations are replaced by a list of comma-separated mode names, each enclosed mode name enclosed by single apostrophes and the entire list enclosed by parenthesis:
|Abbreviation||Modes in list|
|<PHONEMODES>||Modes specified as eligible for DXCC Phone award credit|
|<DXCCDIGIMODES>||Modes specified as eligible for DXCC Digital award credit|
|<WAZDIGIMODES>||Modes specified as eligible for CQ WAZ Digital award credit|
|<WPXDIGIMODES>||Modes specified as eligible for CQ WPX Digital award credit|
|<PSKMODES>||Modes specified to be PSK modes|
|<USERDIGIMODES1>||Modes in the current log's User-specified digital mode family|
These abbreviations enable the use of convenient SQL expressions like
MODE IN <DXCCDIGIMODES>
An 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/content/technologies/jetsql/home.html.
Functions that can be used in SQL expressions are described in
DXKeeper Online Help Contents