CODEWITHSUNDEEP
odbcquickbooks
Oberst
Oberst

Reputation: 477

Trouble with QODBC SQL

Ok. I have been pulling my hair out for the past couple of days trying to do the worlds simplest report...

So after figuring out the table associations, and pulling sample data, it has come to my attention that I need to change up how Im pulling the data. Part of this is pulling Year-to-Date from the Invoice table. However, qodbc is stupid (its probably me, but it makes me feel better to blame the driver)

SELECT * FROM Invoice WHERE TimeCreated > '2014-01-01 00:00:00.000' keeps giving me the error of Invalid operand for operator: >

Searching Google has provided no help for me.
Soooo... I need help with searching by a date field. Any one have ANY ideas or suggestions?

Also, bonus points but related... Anyone else have issues with the speed of the qodbc driver? Some tables can be searched just as fast as mysql, but some tables... Holy crap. 10 mins for a simple query. Ideas for improving the speed of those?

Upvotes: 1

Views: 2251

Answers (2)

Rajendra Dewani
Rajendra Dewani

Reputation: 176

Date Format

SELECT *
from InvoiceLine
WHERE Txndate >= {d '2005-09-23'}

Time Stamps Format

SELECT *
FROM Customer
WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}

SELECT *
from InvoiceLine
WHERE TimeModified >= {ts '2005-09-23 00:00:00.000'}

Refer: http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2203/50/how-are-dates-formatted-in-sql-queries-when-using-the-quickbooks-generated-time-stamps

How to make QODBC running faster

  Keep in mind that QODBC is not a database tool, but rather a translation tool. QuickBooks is a non-normalized flat file system which has no indexes available and will not perform like SQL Server or dBase files. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions.

  Try and keep your result set as small as possible to get a feel for the system, carefully design and test any multi-file joins, and keep the number of returned fields to a minimum for maximum performance.

  Our main goal is to make it easier to access QuickBooks data in a standardised database-like fashion, but queries must be optimized to perform as fast as possible.

  Also, try and use ranges of dates on TxnDate, TxnDateMacro and TimeModified as much as possible to narrow down the data to the smallest possible segment. For example, make something similar to this in the first part of your WHERE clause:

  Invoice.TimeModified >= {ts'2003-09-01 17:01:09'} AND
  Invoice.TimeModified <= {ts'2003-09-02 17:01:09'}

I would suggest to use Optimizer. sp_optimizefullsync All

See: How to setup QODBC Optimizer and where are the Optimizer options for all the details about optimizer. (http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2358/48/how-to-setup-qodbc-optimizer-and-where-are-the-optimizer-options)

  By default the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.

Upvotes: 4

BruceRudd
BruceRudd

Reputation: 126

Never used this driver but try this:

SELECT * FROM Invoice WHERE TimeCreated > {d'2014-01-01 00:00:00.000'}

Might need to muck with the format of the date string a little, just a guess.

As far as the speed of your selects, if the queries have WHERE clauses this can be impacted by not having an index on the table. Tables with indexes will return results faster than tables without.

Upvotes: 0

Related Questions