This post is provided for informational purposes only and the Wizard Systems GoldMine Technical Support team cannot provide or offer any further information or assistance on the topics in this document. All recent versions of GoldMine, allows the user the capability to use SQL Queries to assist with evaluating the data contained in the GoldMine databases.
This capability is available through the SQL Query tab of the Filters and Groups dialog box.
SQL statements consist of building blocks that construct a sentence with which the SQL Query tab communicates with your databases. These statements consist of keywords that indicate an action (such as SELECT, INSERT, UPDATE), operators indicating relations (such as =, +, LIKE, OR, AND) and pointers to tables and columns within your databases (such as CONTACT1, CONTACT1.COMPANY, CONTSUPP.CONTSUPREF).
Groups Based on Queries
Groups can be built based upon the results of SQL queries only if the Accountno field is one of the fields in the SELECT statement. Accountno is a unique field that identifies every contact record in GoldMine individually. It links each contact record to the associated records in other databases, such as Conthist, Contsupp, Cal, etc.
Shadow Fields in GoldMine (U_Fields)
When GoldMine data is re-hosted to an SQL server, U_Fields are created. These fields include U_KEY1 through U_KEY5, U_CONTACT, U_COMPANY, U_LASTNAME and U_CITY. These upper shadow fields contain an exact mirror of the contents of the regular fields that they shadow with one major difference -- the contents of the U_Fields are in UPPER CASE.
It is these fields, not the regular fields that comprise the main indexes on the primary GoldMine contact table. For this reason, it is suggested that queries containing any of the fields that have corresponding U_Fields utilize the U_Fields instead of the regular fields to take advantage of the indexes.
The SELECT Statement
Consider the following example where all data is requested from the Contact1 table.
SELECT * FROM Contact1
Here the wildcard (*) character is used to request ALL of the fields in the Contact1 table. This statement would return the entire Contact1 table displaying all of the fields in a columnar view.
The WHERE Clause
To begin to see the real power of SQL queries, specify that only some fields should be returned and add a WHERE clause to limit the results.
SELECT contact, company, phone1 FROM Contact1 WHERE state = 'Somerset'
This statement will display the contact names, company names, and phone numbers of all contacts that live in Somerset.
This idea can be expanded upon by using comparison operators other than ‘=’ (equal to). These include ‘LIKE [NOT LIKE]’, ‘IN [NOT IN]’, ‘EXISTS’, ‘BETWEEN’, ‘NULL’, ‘ALL’, ‘SOME’ and ‘ANY’. They can be used to refine SQL queries greatly. In addition to these operators, the following table lists common mathematical operators that may be used.
|
Symbol
|
Meaning
|
|
=
|
Is equal to
|
|
<>
|
Does not equal
|
|
<
|
Is less then
|
|
>
|
Is greater than
|
|
<=
|
Is less than or equal to
|
|
>=
|
Is greater then or equal to
|
|
!<
|
Is not less than
|
|
!>
|
Is not greater than
|
The LIKE and NOT LIKE Operator
Consider the following example of the LIKE operator:
SELECT company, contact, phone1 FROM Contact1 WHERE company LIKE '%mine%'
This statement will return all companies containing the text ‘mine’ no matter where it appears in the company name. Companies with names like ‘Goldmine’, ‘Carmine Engineering’, etc. will be returned. The comparison IS case sensitive, however, so a company named ‘Minerals Inc.’ would NOT be returned. To insure that all applicable records are returned, the UPPER function can be used to modify the comparison criteria:
SELECT company, contact, phone1 FROM Contact1 WHERE UPPER(company) LIKE 'MINE%'
With this modification, the all-uppercase version of the company field will be compared to the uppercase ‘MINE’ text using the LIKE operator. Notice the omission of the first % (percent) symbol as well. This change dictates that the statement will only return records whose company field BEGINS with ‘MINE’ as opposed to ‘%MINE%’ which will return all companies that CONTAIN ‘MINE’. Likewise, using ‘%MINE’ would return all companies that END with ‘MINE’.
Similarly, a NOT modifier can be added to the LIKE operator to negate the query and return all companies that do not match the search criteria.
SELECT company, contact, phone1 FROM Contact1 WHERE U_company NOT LIKE 'MINE%'
This query will return all companies that do not begin with ‘MINE’ regardless of the case of the letters involved. Notice that with this query, the U_company upper shadow field is used. This addition will, in all probability, speed up the query because the U_company field is an indexed field.
Since the GoldMine 4.00.9303 release, GoldMine adds information to the Conthist reference field (conthist.ref) to denote linkage of the activities to other contacts. This can become problematic when comparing text literally to the contents of the conthist.ref field as the following query does.
SELECT ref from Conthist WHERE trim(ref) = 'Blank Letter'
This query might not return all activities because the actual contents of the conthist.ref field might be ‘Blank Letter (Tim Wilmot) (oc:Maxine Drew)’ instead of the expected ‘Blank Letter’ value apparent from within the program. Using the LIKE operator instead of ‘=’ (equal to) can insure that the correct records are returned.
SELECT ref from Conthist WHERE trim(ref) LIKE 'Blank Letter%' The IN and NOT IN Operator SELECT company, contact, phone1 FROM Contact1 WHERE TRIM(U_company) IN('GOLDMINE','MICROSOFT','ORACLE,)
The above statement will return all contacts whose uppercase company name is exactly like one of the three listed above. Notice the addition of the TRIM function which will TRIM all spaces off of the company field to facilitate direct comparison of the company field to the values listed in our IN clause.
IN can be negated using NOT in the same manner that LIKE can be.
JOINS
Creating SELECT statements requires a familiarity with the database structures of GoldMine and how the different tables relate to one another. This becomes apparent when attempting to return data from multiple tables.
Consider this statement:
SELECT company, contact, userdef01, userdef02 FROM Contact1, contact2 WHERE contact1.accountno=contact2.accountno
Notice that fields from multiple databases are being selected. Company and Contact are in Contact1 while the Userdef fields are in Contact2. Knowing that Contact1 and Contact2 are tied together via the Accountno field is key to constructing this statement successfully. Having the WHERE function limit the returned results to the records in Contact1 and Contact2 sharing the same Accountno’s guarantees that each row of data returned will be associated with one particular contact and that all four fields displayed will be the data associated with that particular contact. This method is known as a JOIN. Also notice that the WHERE clause specifies which table the fields are in. This is important to note because without using the table.field specification, the query engine cannot determine which Accountno the query is referring to because the FROM clause specifies two tables, both of which contain an Accountno field.
EXPRESSIONS
The results of expressions can be displayed along with static data. Try this:
SELECT company, unumeric1-unumeric2 FROM Contact1, contact2 WHERE contact1.accountno=contact2.accountno
Observe that the Company name and the difference between two numeric user defined field values is displayed for all contacts. The subtraction operation performed in the SELECT statement was able to do this as the data was returned and before it was displayed.
The AND and OR Clause Operators
The addition of an AND or an OR clause can limit the results of queries by using more than just one simple criteria. Consider this statement that lists all activities (contact, phone, reference, and date) completed in the space of one year.
SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '01/01/10' AND conthist.ondate < '01/01/11'
Now consider this query that lists all activities completed in January OR March.
SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND ((conthist.ondate > '01/01/10' AND conthist.ondate < '31/01/10') OR (conthist.ondate > '01/03/10' AND conthist.ondate < '31/03/10'))
Notice the usage of parenthesis to group operations and the use of the OR to specify that either of the date ranges is acceptable.
ORDER BY
An ORDER BY clause can be added to sort the query results by completion date as well:
SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '21/03/98' AND conthist.ondate < '01/04/02' ORDER BY conthist.ondate
More than one field can be specified in the ORDER BY clause to provide multiple sort levels such as:
SELECT contact, phone1, ondate, ref FROM Contact1, Conthist WHERE contact1.accountno=conthist.accountno AND conthist.ondate > '01/01/10' AND conthist.ondate < '01/01/11' ORDER BY conthist.ondate DESC, contact1.contact
. . . which will sort the data in the same manner as the previous query adding the contact field as a secondary sort after the primary sort on the completion date. Also notice the DESC modifier in the ORDER BY clause. This will specify that the primary sort, conthist.ondate, will sort in DESCending order. ASC is used in the same way to denote an ASCending sort, although this is the default.
