GM crashes when running a SQL query. 1

[XopherS]

I'm querying our GM DB (dBase, ~180k contact records) to get info from the Contact1 and Contact2 tables. I want to get all prospects who've been entered into the system after a certain date. Here's the query:

SELECT Contact1.Contact, Contact2.Udldate
FROM Contact1, Contact2
WHERE Contact2.Udldate > '11/20/2006'

For whatever reason, this query causes GM to hang. (Or I'm impatient; it runs for about 7 minutes before I give up and kill it.) I can run queries on the Udldate field in Contact2 with no problem, takes about a minute to run. (I have the Stopwatch add-on for Firefox, so these times are fairly accurate.) So it seems that it's querying from the two tables that's causing it all to take forever.

What gives?

 

[goner]

try:

SELECT Contact1.Contact, Contact2.Udldate
FROM Contact1, Contact2
WHERE contact1.accountno=contact2.accountno
AND Contact2.Udldate > '11/20/2006'

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[XopherS]

That worked! Thanks!!

Why did linking accountno work?

 

[goner]

It tells the query engine exactly how to figure out which contact2 record to return to match up with the contact1 record.

Accountno is the primary key on the accoutno table and is how it relates to the other tables.

Check out this document I wrote on the subject back when I worked for GoldMine:

http://support.frontrange.com/support/goldmine/535_SQLQueriesinGoldMine.htm

It walks you through making simple queries through some moderately more complex ones.

You might also be interested to note that version 6.7 (and higher) has a wizard that can build SQL queries for you.


Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[XopherS]

I used the query wizard to start with, but it wouldn't always work, and frankly I have a vague mistrust for wizards anyways. (I'm anal-retentive in the weirdest ways.) I used it as a starting point to relearning SQL, and am currently creating all sorts of nifty reports based on it. (Mmm, subqueries...)

For all its faults, I do like GM for allowing me to be very, very picky by using SQL.

 

[XopherS]

And wow, I could've used that link you provided ages ago. Thanks again!

 

[goner]

Well, I only wrote it in like 1999 or something!

Various other useful documents here:

http://support.frontrange.com/freesupport/gm_techdocs.asp

Not to mention the myriad useful items on my own site:

http://www.castellcomputers.com/archives.php

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[XopherS]

Hey, I just started using GM like a month ago.

 

[goner]

Well, welcome to the party!

By the way, there's a much more active GoldMine forum here:

http://forums.frontrange.com/viewforum.php?f=6

Doug Castell
Castell Computers

http://www.castellcomputers.com