The query operation is the method for creating a guided query against an entity. The select statement in the request enables you to specify the selection criteria, entity properties, sort order, and pagination. It is similar to a pared down SQL query select statement with constraints in place to ensure the request doesn’t overload server-side resources. As such, the following are query operation limitations:
The query operation supports the GET method. The select statement is specified as a URI parameter.
|HTTP Method||URI Format and Example|
A successful response body contains a <QueryResponse> element corresponding to the SELECT statement in the request. The <QueryResponse> element contains the entities that match the selection criteria of the select statement in the request. The <QueryResponse> element can include the following attributes:
Here's an example of a successful query response body:
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z"> <QueryResponse startPosition="10" maxResults="2"> <Customer> <Id>2123</Id> <SyncToken>0</SyncToken> ... <GivenName>Srini</GivenName> </Customer> <Customer> <Id>2124</Id> <SyncToken>0</SyncToken> ... <GivenName>Peter</GivenName> </Customer> </QueryResponse> </IntuitResponse>
If the query operation detects an error, then the <QueryResponse> element contains the <Fault> element, for example:
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z"> <QueryResponse> <Fault type="Validation"> <Error code=”100” element="query"> <Message>OBJECT name not available </Message> </Error> <Error code="300" element="query"> <Message>Time value is incorrect</Message> </Error> </Fault> </QueryResponse> </IntuitResponse>
The maximum number of entities that can be returned in a response is 1000. If the result size is not specified, the default number is 100. If a query returns many entities, fetch the entities in chunks, as described in Pagination. To determine the number of entities that a particular query returns, probe by using the COUNT keyword in the query. See Count for details.
In the query language, reserved words, entity names, and property names are case insensitive. Additionally, property values are case insensitive. For example, although the following two statements use different cases, they are equivalent:
SELECT * FROM Customer WHERE GivenName = 'greg' STARTPOSITION 10 SELECT * from cUstomer where givenname = 'Greg' Startposition 10
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 * FROM Customer WHERE CompanyName= ‘Adam\’s Candy Shop’
The filter in the WHERE clause determines which entities are returned by a query. The filter criteria is based on the value of a property. For example, the following query returns all Invoice entities with a TotalAmt property greater than 1000.0:
SELECT * FROM Invoice WHERE TotalAmt > '1000.0'
Note that not all properties support filters. To see which properties support filters, go to the entity's reference page to check filtering support for a particular property.
Multiple filters are AND'd together. Note that the OR operation is not supported. The next query returns all Invoice entities created during the specified time range:
SELECT * FROM Invoice WHERE MetaData.CreateTime >= '2009-10-14T04:05:05-07:00' AND MetaData.CreateTime <= '2012-10-14T04:05:05-07:00'
The following statement has two AND operators:
SELECT * FROM Invoice WHERE id in ('64523', '18761', '35767') AND MetaData.CreateTime >= '1990-12-12T12:50:30Z' AND MetaData.LastUpdatedTime <='1990-12-12T12:50:30Z'
If the WHERE clause is omitted, all entities, up to the maximum number, are returned. If a query returns many entities, you should fetch the entities in chunks, as described in Pagination.
Get all invoices for a given customer. Filter on the CustomerRef attribute from the invoice:
SELECT * FROM Invoice WHERE CustomerRef = '123'
Get all active and inactive customers (only list entities like Customer, Vendor, Item, and so on, support concept of Active and InActive)
SELECT * FROM Customer WHERE Active IN (true, false)
Get all active Customer entities:
SELECT * FROM Customer WHERE active = true
Get the Customer entity for the specified identifier:
SELECT * FROM Customer WHERE Id = '123456'
Get all properties for the Customer entities whose first name starts with "K" and ends with "h", and whose last name starts with "Palm".
SELECT * FROM Customer WHERE GivenName LIKE 'K%h' AND FamilyName LIKE 'Palm%'
Get the header properties for the Invoice entities whose CustomerId properties match the values of the IN clause.
SELECT * FROM Invoice WHERE CustomerId IN ('12')
Get all properties for the Invoice entities with transaction dates between Jan. 1, 2011 and the current date.
SELECT * FROM Invoice WHERE TxnDate > '2011-01-01' AND TxnDate <= CURRENT_DATE
To sort the results of a query, include the ORDERBY clause, specifying a property as the sort field. Note that not all properties can be used for sorting. To see which properties can be sorted on, go to the entity's reference page to check sorting support for a particular property.
For example, the following query sorts Customer objects by the FamilyName property:
SELECT * FROM Customer ORDERBY FamilyName
The default sort order is ascending. To indicate descending sort order, include DESC, for example:
SELECT * FROM Customer ORDERBY FamilyName DESC
To page through the results, specify STARTPOSITION (position of the entity in the query results) and MAXRESULTS (maximum number of entities in the result).
For example, suppose you have 25 invoices. The following query gets invoices 1 - 10:
SELECT * FROM Invoice STARTPOSITION 1 MAXRESULTS 10
The next statement gets invoices 11 - 20:
SELECT * FROM Invoice STARTPOSITION 11 MAXRESULTS 10
The following query gets invoices 21 - 25:
SELECT * FROM Invoice STARTPOSITION 21 MAXRESULTS 10
To find out how many object will be returned by a query, specify the COUNT keyword. The number of entities is returned in the totalCount attribute of the <QueryResponse> element. The QuickBooks Online data objects, such as Customer, are not in the response.
The following query returns the total number of customers that belong to the QuickBooks Online company:
SELECT COUNT(*)FROM Customer
The following shows the simplified syntax of a select statement.
Select Statement = SELECT * | count(*) FROM IntuitEntity [WHERE WhereClause] [ORDERBY OrderByClause] [STARTPOSITION Number] [MAXRESULTS Number]
|*||All fields are returned. Projections are not supported.|
|count(*)||The number of records that satisfy the query criteria, returns a single value.|
|IntuitEntity = Customer | Vendor | Invoice | etc.||The IntuitEntity is the name of the entity. Only one entity may be specified. Case sensitive.|
|WhereClause = PropertyName|
| Operator Value [AND WhereClause]*
|The WhereClause filters the returned data according to the value of a property. |
Note: Multiple clauses (filters) are AND'd. The OR operation is not supported.
|Operator = = | IN | < | > | <=| >= | LIKE||Wild card character support with LIKE clauses is limited to "%" (Wildcard that substitutes for zero or more characters).|
Value = (Value [,Value])
This construct is supported only with IN clauses.
|OrderByClause = PropertyName [ASC | DESC]|
|The OrderByClause is for sorting the result|
|Number||A positive integer.|
Got Questions? Get Answers in our developer forums.