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.


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 (\).


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>




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)


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







9 responses to “Advanced SQL Queries”

  1. Peter Rosser Avatar

    Are grouping constructs and OR support coming in the future?

  2. Scott Powell Avatar

    I am trying to query Invoices by by Customer. According to the documentation the CustomerRef field is Filterable. Based on the documentation I have found I tried the query: SELECT * FROM Invoice WHERE CustomerRef.value = ‘customerId’

    I get back a fault with the following error:
    “Error”: [
    “Message”: “Invalid query”,
    “Detail”: “QueryValidationError: Property CustomerRef.value not found for Entity Invoice”,
    “code”: “4001”

    Clearly, I seem to be missing something. Can you enlighten me?

  3. Jeremy Avatar

    “Use backslash () to escape special characters like apostrophe (‘)”

    Lol your example backslash was escaped.

    1. Lisa Rathjens Avatar
      Lisa Rathjens

      Ooops! Thanks, Jeremy, for pointing that out to us. Fixed now!

  4. Darshana Avatar

    Is there going to be support for selecting only required columns in query, instead of using asterisk *”?

    1. Andrew Avatar

      You can already specify the list of fields you’d like returned.

    2. Patty Avatar

      This functionality is not officially supported and there are no plans in the future.

  5. Andrew Avatar

    How should non-ASCII characters be encoded?

  6. nimisha Avatar

    @andrew- You can try escaping them using / but querying on Non- ASCII is not supported in where clause.
    So, best option is to do a select * query and then loop through the response set to get what you need.

Leave a Reply

Your email address will not be published. Required fields are marked *