The GoldMine SQL editor is very useful but it doesn’t always behave the way that you might expect. This is because it’s adding something extra, which can be good…or a bit bad, depending on what you’re trying to do. It particularly messes up aggregate queries (sum, maximum etc). Thankfully, there are ways round the problem.

Firstly, we need to know what’s going on. You can see what’s being passed between GoldMine and SQL by starting up a Profiler session from within SQL Management studio.

A simple query on the cal table, like this one, is passed unchanged:


select ondate from cal


but one that links cal to contact1 or contact2 isn’t:


select ondate from cal, contact1 where contact1.accountno=cal.accountno


This is actually passed to SQL as:


select ondate, contact1.accountno from cal, contact1 where contact1.accountno=cal.accountno


The accountno field is doing a useful job; it is there so that when you click on any of the result, the contact record will change to the appropriate record. The greater mystery then is why the programmers decided to only do this when linking to contact1 or contact2 as, after all, cal itself contains the accountno. That’s a question to which we’ll never know the answer!

So, it’s a good thing then? The answer is: sometimes. Consider this aggregate query that references both the opmgr and contact1 tables:


select sum(foramt) from opmgr, contact1
where contact1.accountno=opmgr.accountno and
(rectype='O' or rectype like 'OZ%') and
startdate>'2016-03-01' and startdate<'2016-10-21' and (userid = 'gemma' or userid = 'rebecca') and key1='domestic enquiry'

We are trying to do a simple sum. If you run this in SQL Management Studio then it will return a value, as expected. If you run it from GoldMine, however, you will get the following error:


Column 'contactl .ACCOUNTNO' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.


And that's because of that pesky accountno!

In order to do what we want, we just have to re-write the query so that we're not linking the two tables:


select sum(foramt) from opmgr
where
(rectype='O' or rectype like 'OZ%') and
startdate>'2016-03-01' and startdate<'2016-10-21' and (userid = 'gemma' or userid = 'rebecca') and accountno in (select accountno from contact1 where key1='domestic enquiry')

With some queries, however, no amount of tinkering or lateral thinking will get rid of the unwanted accountno. Even simple queries joining contact1 to conthist can be impossible to write. For those cases there is a very simple solution: create views that mirror the main tables. I've called mine c1, c1, pending, hist and supp but you can, of course, call them anything you like. You can reference these in the GoldMine query editor with no problem at all and, because GoldMine doesn't recognise them, then it doesn't stick its grubby fingers in and interfere. And, as long as you yourself include the accountno in any simple list queries (as opposed to aggregates) that you write, then the contact record will still move in step.