How to seek a NOT IN RANGE condition

[roslyn]

I'm trying to build a report which highlights the Inactive Suppliers within the ERP database and have the following query which I've built but am struggling to find/apply a "not in range" condition.

SELECT DISTINCT oas_docline.el2 AS [Supplier Code], oas_element.name AS [Supplier Name], oas_element.adddate AS [Date Created], oas_element.moddate AS [Last Modified Date], oas_element.usrname AS [Modified By], oas_element.cur, oas_element.terms AS Terms, IIf([elec]=76,"Yes","") AS [Electronic Payments], IIf([paper]=75,"Yes","") AS [Paper Payments], oas_element.medcode AS [Payment Method], IIf([defbank]=1,"Yes","No") AS [Default Bank], IIf([elec]=76,[bankname],"") AS Bank, IIf([elec]=76,[acname],"") AS [Account Name], IIf([elec]=76,[acref],"") AS [Account Ref], IIf([elec]=76,oas_elmbanklist.sort,"") AS SortCode, IIf([elec]=76,[acnum],"") AS [Account Number]
FROM (((oas_docline INNER JOIN oas_dochead ON (oas_docline.docnum = oas_dochead.docnum) AND (oas_docline.doccode = oas_dochead.doccode) AND (oas_docline.cmpcode = oas_dochead.cmpcode))
INNER JOIN oas_element ON oas_docline.el2 = oas_element.code)
INNER JOIN oas_elmaddrlist ON (oas_element.elmlevel = oas_elmaddrlist.elmlevel) AND (oas_element.code = oas_elmaddrlist.elmcode) AND (oas_element.cmpcode = oas_elmaddrlist.cmpcode))
INNER JOIN oas_elmbanklist ON (oas_element.elmlevel = oas_elmbanklist.elmlevel) AND (oas_element.code = oas_elmbanklist.elmcode) AND (oas_element.cmpcode = oas_elmbanklist.cmpcode)
WHERE (((oas_docline.el2) Like [P%,E%,or %]) AND ((oas_element.adddate) Is Not Null) AND ((oas_element.cmpcode)="MASTER11")
AND ((oas_dochead.yr)>=2008) AND ((oas_dochead.status)<>79)
AND ((oas_element.elmlevel)=2) AND ((oas_element.deldate) Is Null)
AND ((oas_element.matchable)=76)
AND ((oas_element.custsuppacc)=76)
AND ((oas_element.endyear)<=0))
OR (((oas_docline.el2) Like [P%,E%,or %])
AND ((oas_element.adddate) Is Not Null)
AND ((oas_element.cmpcode)="MASTER11")
AND ((oas_dochead.yr)<=2010)
AND ((oas_dochead.status)<>79)
AND ((oas_element.elmlevel)=2)
AND ((oas_element.deldate) Is Null)
AND ((oas_element.matchable)=76)
AND ((oas_element.custsuppacc)=76)
AND ((oas_element.endyear)<=0))
ORDER BY oas_docline.el2, oas_element.medcode DESC;


The above code works for where BETWEEN YYYY and YYYY is specified but I'd like to flip it on it's head and do a NOT IN RANGE query. My concern being that if I simply do a NOT BETWEEN it might bring back data outside that range but some of which may actually exist within the range specified, and I want the results to only bring back el2 codes which exist in oas_element but that aren't used/found in the date range specified on the dochead/docline tables.

Each time I try and specify OUTER JOIN it comes back with either an error indicating "circular reference" or something similar.


Any suggestions would be warmly welcomed!

Many thanks in advance

[Delerna]

Thats one huge query and to try and unravel that and write it to do what you want, without any data, would be extremely difficult ...... for me at least.

I can give a suggestion however. I hope you can make sense of it.

You need the query to still select the data between YYYY and YYYY as you have stated.

What you need to do is left join the "dochead/docline" combination to oas_element and then select the records where a field from "dochead/docline" is equal to null.

If that field is null then it means there was no matching record in "dochead/docline" between your dates for the record in oas_element , and these are the records you want
The field you choose to test for null does, of course, need to have the proviso that if there was a matching record, then it couldn't be null.
In other words, you can't choose a field that might contain a null value in the table itself.

Does that make sense?


I would suggest that you pull the "dochead/docline" part of your query, along with its date range test, out into it's own query and then reference that query in this one. That should make it a bit easier to get your head around the main query

You can always combine them into one query once you get it working properly.

good luck