ODBC Query question.

SOLVED

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%##'    } ???

 

Parents
  • 0

    Are you using Linked Tables or SQL Specific Pass Through Queries

  • 0 in reply to BigLouie

    I am using Visual Studio Dot Net VB and ADO.NET to query MAS directly for a custom tool.

    No hard links whatsoever. 

    I do not use MAS 100 erp myself.  I write in house applications for diverse topics and departments.  Typically in Visual Studio.

    Most of them glean some information from the MAS database at one point or another.

    Honestly I have been following my nose using the ProvideX ODBC driver without good documentation.

    I've only found one skimpy document on the topic.

    In the function below the variable sTargetStatus resolves to:

    ('D01,'D02','D03','D04','D05',...'D15','L01','L02','L03','L04',...'L15','PRO')

    --- Full query as a Function follows. ---

     Public Function GET_HISTORIC_JOBS_BY_DATE_QUERY(ByVal sTargetStatus As String, ByVal sDate1 As String, ByVal sDate2 As String) As String

       Dim s As String = ""

       s &= "SELECT '' AS Blank," & vbCrLf

       s &= "'' AS GroupID," & vbCrLf

       s &= "{fn Right(TRH.SalesOrderNo,5)} + '-' + TRH.WTNumber AS WorkTicketNo," & vbCrLf

       s &= "TRH.NewStatus," & vbCrLf

       s &= "TRH.OldStatus," & vbCrLf

       s &= "{fn Left(TRH.TransactionTime,2)} + ':' + {fn Right(TRH.TransactionTime,2)} AS TRTime,"

       s &= "TRH.TransactionDate," & vbCrLf

       s &= "'' as Price " & vbCrLf

       s &= "FROM JT_TransactionHistory TRH" & vbCrLf

       s &= "WHERE TRH.OldStatus IN " & sTargetStatus & vbCrLf

       s &= "AND TRH.NewStatus <> TRH.OldStatus" & vbCrLf

       s &= "AND TRH.NewStatus IS NOT Null" & vbCrLf

       s &= "AND TRH.TransactionDate BETWEEN {d '" & sDate1 & "'} AND {d '" & sDate2 & "'}" & vbCrLf

       s &= "AND TRH.WTStep = '000'" & vbCrLf

       s &= "AND TRH.RecordType = 'ST'" & vbCrLf

       s &= "ORDER BY TRH.OldStatus ASC, TRH.TransactionDate ASC, TRH.TransactionTime ASC, TRH.SalesOrderNo ASC, TRH.WTNumber ASC" & vbCrLf

       Return s

     End Function

  • 0 in reply to Gruff

    The final result of the query would be the following (With the status code full range of course.)

    ----

       SELECT ' ' AS Blank,

       ' ' AS GroupID,

       {fn Right(TRH.SalesOrderNo,5)} + '-' + TRH.WTNumber AS WorkTicketNo,

       TRH.NewStatus,

       TRH.OldStatus,

       {fn Left(TRH.TransactionTime,2)} + ':' + {fn Right(TRH.TransactionTime,2)} AS TRTime,

       TRH.TransactionDate,

       ' ' as Price

       FROM JT_TransactionHistory TRH

       WHERE TRH.OldStatus IN ('D01,'D02','D03','D04','D05',...'D15','L01','L02','L03','L04',...'L15','PRO')

       AND TRH.NewStatus <> TRH.OldStatus

       AND TRH.NewStatus IS NOT Null

       AND TRH.TransactionDate BETWEEN {d '2014-11-01'} AND {d '2015-01-01'}

       AND TRH.WTStep = '000'

       AND TRH.RecordType = 'ST'

       ORDER BY TRH.OldStatus ASC, TRH.TransactionDate ASC, TRH.TransactionTime ASC, TRH.SalesOrderNo ASC, TRH.WTNumber ASC

  • 0 in reply to Gruff

    You have stated this:

    Gruff said:

    I have to run several queries on SalesOrder and SalesOrderDetail in both current and history tables on a list of job status codes.

    Yet in your code it says.  "FROM JT_TransactionHistory TRH"

  • 0 in reply to BigLouie

    Sorry for the confusion.  

    This query only accesses JT_TransactionHistory.

    I decided not to pursue original question as the query I posted was of more immediate importance.

  • 0 in reply to Gruff

    So I am now more confused. Do you have an issue or not and what is the issue?

  • 0 in reply to BigLouie

    Same original question it Just applies to the JT_TransactionHistory query.

    Is there a better way to filter for a list of status codes than using.

    WHERE  OldStatus IN ('D01', 'D02',... 'D15'.  ,'L01', 'L02', ... 'L15')

    Which appears to be really slow to process.

    I am hoping there is a way to use the LIKE operator to check for the pattern instead.

  • 0 in reply to Gruff

    In older versions of MAS (which means older versions of ProvideX ODBC) I've used LIKE in the form of % for multiple character wildcard and _ for single character wildcard. Either one of these I think:

    WHERE  OldStatus LIKE 'D%' OR OldStatus LIKE 'L%'

    WHERE  OldStatus LIKE 'D__' OR OldStatus LIKE 'L__'

  • 0 in reply to tmelmo

    I have found that the SOTAMAS90 ODBC driver cannot handle complex query statements very well. What I do is create a MS Access database, use SQL Specific Pass Through Queries and then build complex queries off of that. This method is actually faster.

  • 0 in reply to BigLouie

    Thanks tmelmo,  I wish it were that simple.  I have other status codes that start with D or L that I do not want returned.  LQC or DRW are just a few I want to not allow.

    Thank Louie,  I will looking it if I can.  As I am running all this from VB.NET I would have to automate Access from VB.NET to run the query there... I compile and deploy my apps for install all around the company.  Sounds a bit convoluted for my situation.

    In any case do you have a link or two to creating a pass through query?  Never done that before.

  • 0 in reply to Gruff

    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.

Reply
  • 0 in reply to Gruff

    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.

Children
No Data