guild Query help

Help on building queries

An SQL query is constructed using the information you enter in the "build query" window. In general, the window presents you with several lines of "qualifiers" which may be used to limit the query based on the values of variables in the database record. If a checkbox at the left edge of the window is red, then that qualifier will be used to limit the query. You may click on these checkboxes to enable/disable qualifiers.

For text variables, the following comparisons are available. Note that text comparisons are NOT case-sensitive by default, but can be made so using the option menu just above the qualifiers area.

• "is": The variable must match the specified value exactly.
• "is null or" The variable must match the specified value exactly, or else be null (i.e. not specified when the database entry was made).
• "is one of": The variable must exactly match one of a list of values, which may be separated by spaces and/or commas. (The values themselves must not contain spaces or commas.)
• "is not": The variable must NOT match the specified value exactly.
• "is not any of": The variable must NOT exactly match any item in a list of values, which may be separated by spaces and/or commas. (The values themselves must not contain spaces or commas.)
• "matches": The variable must match the specified pattern. Unix-style wildcards * and ? may be used; they will be translated into SQL-style wildcards.
• "does not match": The variable must NOT match the specified pattern.
• "contains": The variable must contain the specified string, which may have embedded spaces and/or commas. Unix-style wildcards * and ? may be used.
• "contains one of": The variable must contain at least one of a list of strings, separated by spaces and/or commas. (The strings must not contain any spaces or commas.)
• "contains all of": The variable must contain all of the strings in the specified list.

For numeric variables, the comparisons are pretty self-explanatory. When doing a "between" comparison, you must enter two values separated by the word AND. (The AND will be inserted for you when you select "between".)

Times may be specified in GPS seconds or as an arithmetic expression giving GPS seconds, such as "630454213-21*60*60". Times may also be specified as nearly any unambiguous date-time string, such as "jan 1 2001" (midnight at the beginning of January 1 in the year 2001), "Jan 15 5:04" (5:04 on January 15 of the current year), "1/19/01 5:15", "19-jan-2001 18:45 cst", "today 3:24pm pdt", "now", "now-1000sec", "now -1hour", "now - 3 days", etc. Times are assumed to be UTC unless you specify a time zone.

A "List" button is provided next to some qualifiers. When pressed, this generates a list of all distinct values of the variable in question. Double-clicking on an item in the list adds it to the entry area for the appropriate qualifier.

If you enter a value which is wider than the entry area provided, you can scroll the contents by dragging with the middle mouse button. Pressing causes the SQL query to be refreshed (but NOT submitted) To submit a query, you must click with the mouse on the "Refresh & Submit" button.