Advanced SQL Queries

We have been getting a lot of questions lately about constructing queries for QBO V3 Services. This article showcases some of the more popular inquiries of late.  For full documentation about constructing queries and submitting them to data services, consult Query Operations in the QuickBooks Online documentation.

Intro

QBO V3 data services supports queries via a unified expression language, optimized for querying CRM data.  This language has a proprietary, SQL-like syntax, familiar to any developer with database experience.  For example, the following statement retrieves all active customers:

SELECT * FROM Customer

In turn, the app issues a HTTP REST GET operation to the query endpoint, passing the query statement as a URL parameter. The resulting URI is as follows:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer

Make sure to URL encode these queries before sending the request down the wire to our servers. There are several online tools to help you do that. Also, check with your specific development environment for their URL encoding support. For additional information about URL encoding, see http://www.w3schools.com/tags/ref_urlencode.asp.

Additionally, surround multi word names with single quotes (‘). For example, to query for Customer objects whose company name is Wild Garden Restaurant, issue the following:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer WHERE CompanyName=’Wild Garden Restaurant’

How Proprietary is Proprietary?

The QBO V3 data services SQL-like syntax has the following limitations:

• The response set returned for an object is limited to full complement of properties.
• Projections are not supported.
• Only those properties with values are returned for the object.
• Joins are not supported.
• Wild card character support with LIKE clauses is limited to “%”.
• The OR operation is not supported in WHERE clauses.

Escape Character

Use backslash (\) to escape special characters like apostrophe (‘). For example, to find a Customer object whose company name is Adam’s Candy Shop, submit the following query:

Select statement:

select * FROM Customer WHERE CompanyName=’Adam\’s Candy Shop’

Request URI:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer WHERE CompanyName= ‘Adam\’s Candy Shop’

Here, the apostrophe in “Adam’s” is escaped with a backslash (\).

Filters

Documentation for each entity clearly denotes those properties available to be used as filters in query WHERE clauses.   You receive an error response similar to the following if you try to filter your query with a non-filterable property—in this case an attempt to filter with Taxable:

<IntuitResponse xmlns=”http://schema.intuit.com/finance/v3″ time=”2014-03-20T10:06:01.401-07:00″>

  <Fault type=”ValidationFault”>

<Error code=”4001″>

<Message>Invalid query</Message>

<Detail>QueryValidationError: property ‘Taxable’ is not queryable</Detail>

</Error>

</Fault>

</IntuitResponse>

Active Name List Entities

Name list entities such as Account, Class, and Customer are either active or inactive as indicated by their <Active> attribute setting.  They are never deleted.  As such, the query

SELECT * FROM Customer 

returns only active customer objects by default. It’s as if the following quere were issued:

SELECT * FROM Customer WHERE Active = true

To retrieve inactive objects you must explicitly filter with the Active property, for example:

SELECT * FROM Customer WHERE Active = false

To retrieve all customers, both active and inactive, issue this query:

SELECT * FROM Customer WHERE Active IN (true,false)

Summary

Have fun with your queries! If there are additional questions I can cover for you, please leave a comment below.