I am trying to write a report to select records that have a certain detail record in Goldmine 'PGDIPMGTST' but not another detail record 'MBA'. I have an SQL query that I have written in Goldmine itself that will do the job:

SELECT  *  FROM  CONTACT1 WHERE Contact1.ACCOUNTNO IN (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'Graduate' AND  CONTSUPP.CONTSUPREF = 'PGDIPMGTST')) and contact1.accountno not in (SELECT DISTINCT ACCOUNTNO FROM CONTSUPP WHERE (CONTSUPP.RECTYPE = 'P' AND CONTSUPP.CONTACT = 'Graduate' AND  CONTSUPP.CONTSUPREF LIKE '%MBA%'))

I cannot get a version of this query to work in stonefield. I have tried using the Advanced tab and executing part of this query in the "Specify Before data Code" area

SELECT * FROM CONTACT1,CONTSUPP WHERE CONTACT1.ACCOUNTNO NOT IN (select CONTSUPREF.ACCOUNTNO from CONTSUPP where CONTSUPREF like '%MBA%' and CONTSUPP.RECTYPE = 'P' and CONTSUPP.CONTACT = 'Graduate')

When this code runs it comes up with an error "contact1.dbf does not exist"

Any assistance appreciated.

For anyone from Stonefield reading this, it would help if your developers considered this issue. This is what I would have thought to be a simple task, yet it is very difficult in Stonefield.

This can be done by editing the SQL statement in step 2 of the Advanced Settings and by using filters to simplify the SQL statement.

1) Add filters on Detail.Detail equals Graduate AND Detail.Reference equals PGDIPMGTST. This will simplify a stlot of the SQL statement

2) Edit the SQL in step 2 of the advanced settings to something like the code below (yours will be different in the field list if you have different fields on the report)

select CONTACT1.CONTACT,DETAILS.CONTACT as CONTACT_A,CONTACT1.COMPANY,DETAILS.CONTSUPREF from DETAILS inner join CONTACT1 on DETAILS.ACCOUNTNO=CONTACT1.ACCOUNTNO where CONTACT1.ACCOUNTNO not in (SELECT DISTINCT ACCOUNTNO FROM DETAILS WHERE (DETAILS.RECTYPE = 'P' AND DETAILS.CONTACT = 'Graduate' AND  DETAILS.CONTSUPREF LIKE '%MBA%')) order by 2

If you let me know your email I can send you a sample report with this SQL statement.

This can be done by editing the SQL statement in step 2 of the Advanced Settings to something like the code below (yours will be different in the field list if you have different fields on the report)

select CONTACT1.CONTACT,CONTACT1.COMPANY,DETAILS.CONTACT as CONTACT_A,DETAILS.CONTSUPREF from DETAILS inner join CONTACT1 on DETAILS.ACCOUNTNO=CONTACT1.ACCOUNTNO WHERE (DETAILS.RECTYPE = 'P' AND DETAILS.CONTACT = 'Graduate' AND  DETAILS.CONTSUPREF = 'PGDIPMGTST') and CONTACT1.ACCOUNTNO not in (SELECT DISTINCT ACCOUNTNO FROM DETAILS WHERE (DETAILS.RECTYPE = 'P' AND DETAILS.CONTACT = 'Graduate' AND  DETAILS.CONTSUPREF LIKE '%MBA%')) order by 2

If you let me know your email I can send you a sample report with this SQL statement.

We are also looking into possibly adding a user interface to create 'negative' queries like this for our 3.2 release.