|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Cannot Count Invoiceline Table Records |
| Author |
Message |
|
|
| Posted : 2006-08-09 04:17:01 |
I'm having trouble getting queries to run on my development server using Quickbooks online that have been running fine on my local machine. My local environmnet consisted of my laptop and a test QBO database. Now I'm running the same queries on my dev box while connecting to my live QBO database. After attempting to run the following query in VB Demo, I get an error reading "[QODBC] Error":
SELECT
{fn TIMESTAMPDIFF(SQL_TSI_HOUR, {fn NOW()}, InvoiceLine.TimeModified)} as HoursSinceLastModified,
InvoiceLine.invoiceLineDesc as InvoiceDescrip,
InvoiceLine.refNumber as InvoiceNumber,
Customer.ResaleNumber AS PID,
InvoiceLine.CustomerRefFullName as AccountName,
ItemService.Name as ProductName,
InvoiceLine.Subtotal,
InvoiceLine.SalesTaxTotal,
InvoiceLine.Memo,
InvoiceLine.InvoiceLineAmount,
InvoiceLine.CustomerRefListID
FROM InvoiceLine InvoiceLine,Customer Customer,ItemService ItemService
WHERE {fn TIMESTAMPDIFF(SQL_TSI_HOUR, {fn NOW()}, InvoiceLine.TimeModified)} >= -2
AND Customer.ListID = InvoiceLine.CustomerRefListID
AND ItemService.ListID = InvoiceLine.InvoiceLineItemRefListID
I tried just doing a simple count on the invoiceline table and it returned the same error. I can't even return the top 10 records from that table. I can run queries against other tables, just not invoiceline.
Any thoughts?
Thanks Tom,
Merritt |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-08-09 10:14:55 |
I ran your query without a problem using QuickBooks 2006 Premier Edition as can be seen here:
Because of the nature of your WHERE clause evaluation in your SQL Query, QODBC will do a full table scan on the InvoiceLine table. The QuickBooks Online Edition has a record scan limit of 1000 records only, so you will need to simplify your query to smaller subsets of data like this:
SELECT
{fn TIMESTAMPDIFF(SQL_TSI_HOUR, {fn NOW()}, InvoiceLine.TimeModified)}
as HoursSinceLastModified, InvoiceLine.invoiceLineDesc as InvoiceDescrip,
InvoiceLine.refNumber as InvoiceNumber, Customer.ResaleNumber AS PID,
InvoiceLine.CustomerRefFullName as AccountName, ItemService.Name as ProductName,
InvoiceLine.Subtotal, InvoiceLine.SalesTaxTotal, InvoiceLine.Memo,
InvoiceLine.InvoiceLineAmount, InvoiceLine.CustomerRefListID
FROM InvoiceLine InvoiceLine,Customer Customer,ItemService ItemService
WHERE InvoiceLine.TimeModified > {ts '2006-08-01 00:00:00.001'}
AND {fn TIMESTAMPDIFF(SQL_TSI_HOUR, {fn NOW()}, InvoiceLine.TimeModified)} >= -2
AND Customer.ListID = InvoiceLine.CustomerRefListID
AND ItemService.ListID = InvoiceLine.InvoiceLineItemRefListID
which runs a whole lot faster too! |
|
|
|
|