Filtering the Log Page Display with SQL

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.

An SQL expression typed directly into the Filter panel textbox can be used to immediately filter Log Page Display by striking Ctrl-S.

Constructing SQL Queries

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 query.

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 

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-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.

CNTY="IA,O'BRIEN"

Data Types

Items are representing using one of four data types:

• String (a sequence of alphanumeric characters)

• Date

• 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.

CALL='VK9NS'

and

CNTY="IA,O'BRIEN"

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.

Len Operator

The LEN operator is applied to a string data type to determine the number of characters it contains. For example,

LEN(NAME) > 5

would filter the the Log Page Display to contain only QSOs whose NAME item is of length 5 or greater.

Empty Values

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:

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.

Wildcard Characters

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:

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:

Referencing the contents of the Filter panel textbox

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.

Referencing the ARRL Country Code of the callsign specified in the Filter panel textbox

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 

KH3DX

results in execution of the query

DXCCID = '123'

where 123 is the ARRL Country Code for Johnston Island.

If DXView is running, then "callsign to country code" lookup will reference any relevant Override.

Referencing the Transceiver's current transmit band and receive band

<XCVRTXBAND>

Comments

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 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

USER_10-10='123.45'

would be converted to  

APP_DXKEEPER_USER_DEFINED_2='123.45'

before execution.

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:

These abbreviations enable the use of convenient SQL expressions like 

MODE IN <DXCCDIGIMODES>

References

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 http://www.techonthenet.com/access/functions/

DXKeeper Online Help Contents