Well you can do a compound WHERE clause using phrases like NOT or NOT LIKE, or even NOT(colA LIKE 'value' OR colB LIKE 'value') or just the <> operator (instead of != ). Obviously I have no idea of your full exclusion list but here are some variations that would limit the initial results:

WHERE  (OldStatus LIKE 'D%' OR OldStatus LIKE 'L%') AND NOT (OldStatus='DRW' OR OldStatus='LQC')

WHERE  (OldStatus LIKE 'D%' OR OldStatus LIKE 'L%') AND NOT (OldStatus LIKE 'D_W' OR OldStatus LIKE 'LQ_')

WHERE  (OldStatus LIKE 'D%' OR OldStatus LIKE 'L%') AND OldStatus NOT LIKE 'D_W'

Adjust as needed. Hope that helps.

sagecity.na.sage.com/.../73502.aspx

How to do pass through queries

Well I am back gentlemen.

I found a query that will return what I want without a list of statuscodes.

Unfortunately it only appears to be a few seconds faster than searching by a list of codes.

WHERE {fn Len(TR.OldStatus)} = 3

AND {fn Substring(TR.OldStatus,1,1)} IN ('D','L')

AND {fn Ascii({fn Substring(TR.OldStatus,2,1)})} BETWEEN 48 AND 57

AND {fn Ascii({fn Substring(TR.OldStatus,3,1)})} BETWEEN 48 AND 57

OR TR.OldStatus = 'PRO'

vs

WHERE TR.OldStatus IN ('L01','L02','L03'...'L15','D01','D02','D03'...'D15','PRO')

In my research I did find that ProvideX ODBC does support a CONVERT function.

the bad news is that you have to make sure the text to convert to integer contains only numeric characters.

Because I had to validate with the Ascii function first, Convert did not add much to the query so I ended up not including it.

Example:

AND {fn CONVERT({fn Substring(TR.OldStatus,2,2)},SQL_INTEGER)} BETWEEN 1 AND 20

Maybe it can help someone else in the future.

I am giving up on the problem and sticking with the List method as I am not seeing much improvement in query speed.

Thank you for all the help,