ODBC Query question.

Question

I have to run several queries on SalesOrder and SalesOrderDetail in both current and history tables on a list of job status codes. 
 Processing the queries takes way too much time. 
 The status codes I am interested in start with a letter and end in two digits. 
 AND OldStatus in ('D01', 'D02',... 'D15'. ,'L01', 'L02', ... 'L15') 
 Is there a way to create a query that doesn't need a complete list of status codes? 
 Does LIKE have parameters that could help me out here? 
 {AND OldStatus LIKE 'D%##' OR OldSatus LIKE 'L%##' } ???

Gruff · Accepted Answer

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,

Sage 100

ODBC Query question.

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING