We have been experiencing several issues with the performance of QODBC that require work arounds to try to accomplish something that should be standard sql syntax. The performance on joins with three tables is so slow we have to do a two table join first and then append data. Now we are experiencing an issue with getting 0 records returned for any query using the IN syntax with multiple values. Below are examples of our results.
26 Records: SELECT A.CustomerRefListID, A.TxnNumber, A.TimeCreated, A.ARAccountRefListID, A.ClassRefFullName, A.TxnDate, A.CustomerRefListID, A.CustomerRefListID, A.TimeModified, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.TermsRefFullName, A.IsPending, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.ShipAddressPostalCode, A.ShipAddressCountry, A.IsPending, A.PONumber, A.TermsRefListID, A.TermsRefListID, A.TermsRefFullName, A.DueDate, A.SalesRepRefListID, A.SalesRepRefFullName, A.ShipMethodRefFullName, A.ItemSalesTaxRefListID, A.ShipDate, A.SalesTaxPercentage, A.SalesTaxPercentage, A.SalesTaxTotal, A.BalanceRemaining, A.IsPaid, A.CustomerMsgRefFullName, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, '' FROM INVOICE A, INVOICELINE B WHERE A.TXNNUMBER=B.TXNNUMBER AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} >= {fn CONVERT('2001-07-13',SQL_DATE)}) AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} <= {fn CONVERT('2006-07-13',SQL_DATE)}) AND A.CUSTOMERREFFULLNAME = 'Abercrombie, Kristy:Remodel Bathroom'
24 Records SELECT A.CustomerRefListID, A.TxnNumber, A.TimeCreated, A.ARAccountRefListID, A.ClassRefFullName, A.TxnDate, A.CustomerRefListID, A.CustomerRefListID, A.TimeModified, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.TermsRefFullName, A.IsPending, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.ShipAddressPostalCode, A.ShipAddressCountry, A.IsPending, A.PONumber, A.TermsRefListID, A.TermsRefListID, A.TermsRefFullName, A.DueDate, A.SalesRepRefListID, A.SalesRepRefFullName, A.ShipMethodRefFullName, A.ItemSalesTaxRefListID, A.ShipDate, A.SalesTaxPercentage, A.SalesTaxPercentage, A.SalesTaxTotal, A.BalanceRemaining, A.IsPaid, A.CustomerMsgRefFullName, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, '' FROM INVOICE A, INVOICELINE B WHERE A.TXNNUMBER=B.TXNNUMBER AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} >= {fn CONVERT('2001-07-13',SQL_DATE)}) AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} <= {fn CONVERT('2006-07-13',SQL_DATE)}) AND A.CUSTOMERREFFULLNAME = 'Baker, Chris:Family Room'
0 Records: SELECT A.CustomerRefListID, A.TxnNumber, A.TimeCreated, A.ARAccountRefListID, A.ClassRefFullName, A.TxnDate, A.CustomerRefListID, A.CustomerRefListID, A.TimeModified, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.DueDate, A.TermsRefFullName, A.IsPending, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.TermsRefListID, A.ShipAddressPostalCode, A.ShipAddressCountry, A.IsPending, A.PONumber, A.TermsRefListID, A.TermsRefListID, A.TermsRefFullName, A.DueDate, A.SalesRepRefListID, A.SalesRepRefFullName, A.ShipMethodRefFullName, A.ItemSalesTaxRefListID, A.ShipDate, A.SalesTaxPercentage, A.SalesTaxPercentage, A.SalesTaxTotal, A.BalanceRemaining, A.IsPaid, A.CustomerMsgRefFullName, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, B.TxnID, '' FROM INVOICE A, INVOICELINE B WHERE A.TXNNUMBER=B.TXNNUMBER AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} >= {fn CONVERT('2001-07-13',SQL_DATE)}) AND ({fn CONVERT(A.TimeCreated, SQL_DATE)} <= {fn CONVERT('2006-07-13',SQL_DATE)}) AND A.CUSTOMERREFFULLNAME IN ('Baker, Chris:Family Room', 'Abercrombie, Kristy:Remodel Bathroom')
NOote that the above query should have returned 50 total records (24 + 26).
Below is a very simple example that doesn't work as it should.
108 Records: SELECT * From Customer Where IsActive IN (1)
0 Records: SELECT * From Customer Where IsActive IN (0,1)
The 2nd query should have returned 108 records too.
If the IN syntax doesn't work, then we have to run the query once for each customer name unless we use the OR with each customer name which will give unacceptably long response times . This is standard sql syntax which we would expect to be supported. Please respond to this request as soon as possible, because the syntax is required for the application to perform as it should. |
