Oracle CRM On Demand Documentation Library: QueryPage

Executes a query against the set of records for an object, and returns the subset of the records that match the search criteria set by the method arguments.

Query by Template

To improve performance, the QueryPage result contains only those fields and objects that are included in the QueryPage request. To retrieve the values of fields that are not a part of the search criteria, the field must be included in the search request with a blank value.

Query by Children

For all Oracle CRM On Demand object methods (except Attachment), it is possible to query using one operation within a parent-child relationship. This type of query is called Query By Children. The query can be assembled using parent attributes as well as child attributes. You can query for all children of a particular parent or set of parents, all parents of a particular child or set of children, or for both parents and children of a particular set.

NOTE: When performing a query using Query By Children, certain fields on a child object might not be filterable.

Not all child objects are filterable in Web Services v1.0 (for example, the Partner child of Opportunity).

CAUTION: This type of query performs slowly and must only be used when necessary.

As an example of how you can search for parent records using criteria on a child:

This query would return all contacts where the first name is John and at least one of John's addresses is for Canada. The child criteria are appended to the parent using an AND or OR operator based on the value of the argument UseChildAnd. When UseChildAnd is true, the child criteria are "ANDed", otherwise they are "ORed".

Using the PageSize and StartRowNum Arguments

The PageSize argument, which has a maximum value of 100, is used to specify the maximum number of records to be returned in a QueryPage response.


PageSize	StartRowNum	Records Returned	LastPage
100	0	0-99	false
100	100	100-199	false
100	200	200-299	false
100	300	300-399	false
100	400	400-499	false
100	500	500-599	false
100	600	600-699	false
100	700	700-799	false
100	800	800-899	false
100	900	900-997	true

Even though the QueryPage method returns a limited number of records, the results are cached, and can then be retrieved by calling the QueryPage method with a value of PageSize+1 for the StartRowNum argument. To retrieve all records in the result set, StartRowNum must be incremented by PageSize for each subsequent QueryPage request until a QueryPage response is returned with LastPage equal to true.

About Oracle CRM On Demand Query Syntax

The query syntax supports only a small subset of binary and unary operators. No Siebel Query Language constructs or functions are supported. The query syntax is summarized in Table 23.

Table 23. Query Syntax for QueryPage
Syntax Type	Notes
expression
{Operator} {Value}	Operator can be binary or unary. The {Value} need only be specified for binary operators. Every expression must start with an operator to avoid ambiguity. There is no default operator.
`(expression)` `conjunction` `(expression)`	A conjugated expression must be enclosed in parentheses to avoid ambiguity. However, nonconjugated expressions must not be enclosed in parentheses.
conjunction
`OR`	`None`
`AND`	`None`
unary operator	`None`
IS NULL	Used to find a match for a value that has no value
binary operator
`=`	`None`
`~=`	Denotes a case-insensitive exact search (no wildcards used)
`<`	Must be specified as < to ensure well-formed XML.
`<=`	Must be specified as <=
`>`	`None`
`>=`	`None`
`<>`	Must be specified as <>
`LIKE`	Wildcard characters are treated as such only in the context of the operator LIKE.
`~LIKE`	Denotes a case-insensitive wildcard search
value
`'literal'`	Literal data is always enclosed in single quotes. To use a single quote within a literal, place another single quote immediately beside that quote. In this way, the query recognizes the quote as a literal and not as an operator. For example, the string `ab'c` is specified as `ab''c`. To use the special characters such as asterisk (*), question mark (?), and backslash (\) in queries, preceded them with the \ character. For example, to use the ? wildcard operator in a query, precede it with the \ character as follows: \?

CAUTION: Queries that are case-insensitive or with leading wildcard characters will perform slowly and must only be used when necessary. In some cases, you can however use shadow fields, see Using Shadow Fields for Better Search Performance.

Querying Oracle CRM On Demand Data Using Web Services

The QueryPage functions require a list of object instances as input to perform a query. This input argument is called ListOf(Object). For example, the ContactQueryPage method requires the ListOfContact argument. Each ListOf(Object) argument requires at least one instance of the Object to specify a valid query.

To query an object by a certain field, specify the expression that corresponds to the desired result. The examples in this topic cover the corner cases of quote and wildcard escaping. Assume that a table in the Oracle CRM On Demand database contains the following values for a particular column that is being queried:

?abc
abcd
'abc'
= 'abc'
abc?d
abc*d
aBc*D
abcd
abc*d
abc\d
abc\*d
abc\\*d
abc\d
abc\*'d
abc\?"d
abc\*"d
abc\*'"d
(NULL value)

Table 24 specifies the returned record sets for various values of each field value that maps to the preceding list.

Table 24. Returned Record Sets
Field Value	Returned Record Set	Comments
abc	Not applicable	An unquoted value without an explicit operator is invalid input.
'abc'	Not applicable	A quoted value without an explicit operator is invalid input.
= '''abc	''''abc'	None
= "'abc'"	Not applicable	Double quotes are not allowed by the Oracle CRM On Demand Validator. Consequently, this example returns an error message.
= 'abc	'abc	None
= '= ''abc	'''= 'abc'	None
= '= 'abc''	Not applicable	The caller is responsible for correctly formatting quotes in Query* methods. This example does not have correctly formatted quotes, so it results in an error.
= 'abc?d	'abc?d	None
= 'abc\?d	'abc?d	None
LIKE 'abc\?d	'abc?d	None
LIKE 'abc?d	'abc?d abc*d abc\d	None
~LIKE 'abc?d	'abc?d aBcD abcd abc\d	None
= 'abc*d'	abc*d	Any wildcard character that has not been formatted with quotes is treated as if it were formatted with quotes.
= 'abc\*d	'abc*d	None
= 'abc\\*d	'abc\*d	None
LIKE 'abc\\*d	'abc\d abc\d abc\\d abc\d abc\'d abc\?"d abc\"d abc\*'"d	None
= 'abc\\\*"d	'abc\*"d	None
= 'abc\\?"d	'abc\?"d	None
= 'abc\\\?"d	'abc\*"d	None
LIKE 'abc\\?"d	'abc\?"d abc\*"d	None
LIKE 'abc\\\?"d	'abc\?"d	None
LIKE 'abc\\*"d	'abc\?"d abc\"d abc\'"d	None
LIKE 'abc\\\*"d	'abc\*"d	None
= 'abc\\\*''d	'abc\*'d	None
~ LIKE 'abc*\d	'aBcD abcd abcd abc*d	None
LIKE 'abc*\d	'abcd abcd abcd	None
(empty field)	Not applicable	An empty field value does not influence the search specification in Query by Template. NOTE: A query in which all fields are empty fields returns all records.
IS NULL	(empty field) (( > 'abc*' ) AND ( < 'abcd' ))	None
OR (~= 'abc*d')	abcd aBcD abc*d	None
NOT LIKE 'abc?d'	Not applicable	The Oracle CRM On Demand Query Validator does not support the NOT operator, so this query returns an error.
> 'abc' BUT < 'abcd'	Not applicable	BUT is not a valid conjunction. Consequently, this query returns an error.

Querying Multiple Fields

To specify a query on multiple fields, expressions must be provided for each field comprising the search specification. When multiple fields in an object instance have expressions, the QueryPage method result is the intersection of all the expressions, or in other words, all of the expressions are combined using the AND operator. This is outlined in Example 1: Combining Expressions Using the AND Operator.

The Web service client requires the first name, last name, and job title of all the contacts in Oracle CRM On Demand that have a job title equal to CEO and a last name equal to Doe. The XML representation of the ListOfContact object that must be sent in the ContactQueryPage call is as follows:

Multiple Expressions on a Single Field

If you want to apply multiple expressions to a single field, you can combine each expression using either the AND or the OR operator. The result is either the intersection or the union of the object instances respectively.

NOTE: For multiple expressions on a single field, each expression must be enclosed in brackets.

The Web service client requires the first name, last name, and job title of all the contacts that have been updated between July 28, 2009 6:30am and July 28, 2009 6:45 am.

Send the following XML representation of the ListOfContact object in the ContactQueryPage call:

The Web service client requires the first name, last name, and job title of all the contacts in Oracle CRM On Demand that have a last name equal to Doe or Brown.

Send the following XML representation of the ListOfContact object in the ContactQueryPage call:

Specifying Books in Queries

For queries on record types that support books, you can use the BookId or BookName arguments to constrain the query to only the records in a particular book. For more information about the use of books, see Oracle CRM On Demand Online Help.

Arguments

Table 25. Arguments Taken by the QueryPage Method
Name	Description	Required	Default	I/O
ListOf(Object). For example, ListOfAccount	The list of object instances queried (input), and after query execution, the list of object instances returned (output).	Yes	Not applicable	Input/Output
PageSize	The maximum number of records displayed on a page following a query.	No	10	Input
StartRowNum	Indicates the row from which the QueryPage method starts to return records. Use the StartRowNum argument to return a set of records for any given method. For example, if PageSize=100 and you want to return records 1-100, you set StartRowNum to 0. Then, if you want to return records 101-200, you set StartRowNum to 100, and run the query again. You continue doing this until the last page is returned. In this way, you can return all records for a particular query.	No	0	Input
UseChildAnd	If this argument is set to true, the query result set returns the set of records that satisfy both parent and child search criteria. (That is, the query set returned is the AND combination of parent and child queries.) If this argument is set to false (or not set at all), the query result set returns the set of records that satisfy either the parent or the child search criteria. (That is, the query set returned is the OR combination of parent and child queries.)	No	False	Input
BookId	The book Id.	No	Not applicable	Input
BookName	The book name. This argument is ignored if a value for BookId is supplied.	No	Not applicable	Input
IncludeSubBooks	Whether subbooks are to be included.	No	False	Input

Oracle Web Services On Demand Guide, Version 21.0 (Oracle CRM On Demand Release 33)		Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Legal Notices.

QueryPage