GoldMine’s SQL Query Tab has to be one GoldMine’s most important features. Other than using an external report writer or database utility, there is no other way within GoldMine to find and view data from any GoldMine table in a layout of your liking.
What you’ll like about SQL Queries – It’s free because it’s built into GoldMine. (It’s found as a tab on the same window as “Filters & Groups.”) It’s fast because it leverages the performance of MS SQL to query the database. It’s also flexible because of its ability to look anywhere in GoldMine and you can format the result in various ways. It’s accessible because it’s available to you from within GoldMine. And, once the query is written, it is quite easy to use.
The only hard part is constructing (writing) the query. While GoldMine does have a “Query Builder” which is fine for many simple tasks, you’ll find it falls short of even slightly demanding needs. And when it does that’s when someone without specific SQL knowledge and skill is needed. Most organizations lack such abilities. My suggestion is that you either copy the query from someone who’s written it before, or let someone write it for you. You wouldn’t learn French to ask someone to point you to the restroom, so why try to learn Standard Query Language (SQL) to ask GoldMine to point you to data that is worth finding!
If you really want to learn more about SQL Queries, here’s some simple generic information to help you get started:
SQL is almost English; it’s made up largely of English words, put together into strings of words that sound similar to English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work.
The first word of each query is its name, which is an action word (a verb) that tells SQL what you want to do. The query name is followed by words and phrases — some required and some optional — that tells SQL how to perform the action. For instance, you always need to tell SQL what to create, and you always need to tell it which table to insert data into or to select data from.
The following is a typical SQL query. As you can see, it uses English words:
SELECT lastName FROM Contact1
This query retrieves all the last names stored in the Contact1 table in GoldMine. More complicated queries, such as the following, are less English-like:
SELECT lastName FROM CONTACT1 WHERE state=’CA’ AND
city=’Fresno’ ORDER By lastName
This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. Although this query is less English-like, it’s still pretty clear.
FREE OFFER FOR GOLDMINE USERS – GET 20 FREE SQL QUERIES!
First Direct Corp. will send you 20 complimentary (free) SQL expressions that you can copy & paste in to your GoldMine system (Corporate or Premium Edition).
SQL queries can easily answer all kinds of useful questions!
Request your 20 FREE SQL Queries today and start realizing the power of SQL Queries!
If you’d like to learn more in order to write them yourself, here is a link to “The GoldMine Guide to SQL Queries”
Here are some general points to keep in mind when constructing an SQL query, as illustrated in the preceding sample query:
- Capitalization: The case of the SQL words doesn’t matter. For example, select is the same as SELECT, and from is the same as FROM, as far as SQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter. Your database table and column names must match exactly, so the case for the column names has to be correct — for example, lastname isn’t the same as lastName.
- Spacing: SQL words must be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.
- Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are a series of characters called text strings or character strings. You’re asking SQL to compare the text strings in the SQL query with the text strings already stored in the database. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes.
One useful way to use SQL queries with GoldMine is to check for a number of common data issues. Here are several that your GoldMine administrator can check for:
Simply copy & paste in to the SQL Query tab in your GoldMine system(Corporate or Premium Edition) and start using!
No duplicate accountnos in contact1 & contact2
SELECT COUNT(*), accountno
FROM contact1
GROUP BY accountno
HAVING COUNT(*)>1
For each accountno in c1, there should be 1 and only 1 accountno in c2
Missing CONTACT2 records
SELECT *
FROM contact1
WHERE accountno not in (SELECT accountno
FROM contact2 )
Orphaned CONTACT2 records
SELECT *
FROM contact2
WHERE accountno not in (SELECT accountno
FROM contact1 )
Orphaned CONTSUPP records
SELECT *
FROM contsupp
WHERE rectype <> ‘H’ AND
rectype <> ” AND
accountno not in ( SELECT accountno
FROM contact1 )
Duplicate RECIDs in contsupp. linkacct
SELECT *
FROM contsupp
WHERE recid IN( SELECT linkacct
FROM contsupp
GROUP BY linkacct HAVING COUNT(linkacct)>1 )
No duplicate RecID in entire db
(works for all non tlog tables)
SELECT COUNT(*), recid
FROM contact1
GROUP BY recid HAVING COUNT(*) > 1 or recid IS NULL
No duplicate FRecID, fieldname in conttlog & gmttlog
SELECT COUNT(*), frecid, fieldname
FROM conttlog
GROUP BY frecid, fieldname
HAVING COUNT(*)>1 OR frecid IS NULL OR fieldname IS NULL
For each pending email in the Inbox, there should be a CALENDAR (CAL) entry
SELECT * FROM mailbox
WHERE folder = ‘X-GM-INBOX’ AND recid NOT IN(
SELECT mailbox.recid FROM mailbox, cal
WHERE mailbox.folder = ‘X-GM-INBOX’ AND
mailbox.linkrecid = cal.recid )
For each sent or filed email, there should be a HISTORY (conthist) entry
SELECT * FROM mailbox
WHERE (folder = ‘Sent’ OR folder = ‘Filed’) AND
recid NOT IN( SELECT mailbox.recid
FROM mailbox, conthist
WHERE (folder = ‘Sent’ OR folder = ‘Filed’) AND mailbox.linkrecid = conthist.recid )
Check for invalid rectypes in CALENDER (CAL) table
SELECT * FROM cal
WHERE accountno NOT LIKE ‘PB:%’ AND
rectype NOT IN (‘A’, ‘C’, ‘D’, ‘E’, ‘F’, ‘H’, ‘M’, ‘O’, ‘Q’, ‘S’, ‘T’)
All CALENDAR (CAL) entries of type a, c, d, e, f, o, q, s, t should have a userid
SELECT * FROM cal
WHERE accountno NOT LIKE ‘PB:%’ AND
rectype IN (‘A’, ‘C’, ‘D’, ‘E’, ‘F’, ‘O’, ‘Q’, ‘S’, ‘T’) AND (userid = ” OR userid IS NULL)
FREE OFFER FOR GOLDMINE USERS – GET 20 FREE SQL QUERIES!
First Direct Corp. will send you 20 complimentary (free) SQL expressions that you can copy & paste in to your GoldMine system (Corporate or Premium Edition).
SQL queries can easily answer all kinds of useful questions!
Request your 20 FREE SQL Queries today and start realizing the power of SQL Queries!
If you’d like to learn more in order to write them yourself, here is a link to “The GoldMine Guide to SQL Queries“.
