The Readbyquery() method is one of the most used Intacct API calls, because it allows you to retrieve records from a certain object based on a query. The query syntax used in this method is actually a subset of standard SQL where clause. Therefore, it can be confusing as to which standard SQL syntax is applicable to Intacct and which is not. I listed some tips I gathered from my trial and errors. I hope you find them helpful.
- It is a good habit to enclose your query in parenthesis.
- Supported SQL operators include, but are not limited to:
<, >, <=, >=, =, like, not like, in, not in, and, or, is null, is not null, not
- Because Intacct API calls are XML based, the operators ‘<’ and ‘<=’ are illegal as a result. Both operators should be replaced with ‘<’ and ‘<=’ instead.
- Use the escaped form on these characters.
ampersand & -> &
apostrophe ' -> '
quotation mark " -> "
- Use IS NOT NULL if you want to exclude null values.
- Use IS NULL if you only want to return null values.
- If you want to express NOT EQUAL TO, use these expressions.
- (RECORDID NOT IN (‘92’))
- (NOT RECORDID = ‘92’)
- Use IN operator to query within an array. For example, (RECORDNO IN ('11','22','33','44','55','66','77','88','99'))
- Use ‘<=’ and ‘>=’ on a date range.
For example, (WHENDUE <= '02/28/2015' AND WHENDUE >= '02/01/2015')
10. Use the LIKE operator and a wildcard character such as % to substitute for zero or more characters to perform START WITH, END WITH or CONTAIN type of queries.
For example, (VENDORNAME LIKE 'Code%')
I hope you find these useful, please let me know if you have other tips for Readybyquery().