Several Web Services methods, such as doSelect() and doQuery(), require where clauses for searching. A where clause is the string appearing after the ‘WHERE’ keyword in an SQL statement. For example, a where clause to find contacts (the ‘cnt’ object) by last name could be the following:
last_name = ‘Jones’
or
last_name LIKE ‘Jone%’
The second example finds all contacts with names beginning with ‘Jone’, while the first just finds the Jones’.
CA SDM supports only a subset of the standard SQL parameters for where clauses, and are listed as follows:
Parentheses are allowed for grouping. Explicit joins, EXISTS, and GROUP BY elements are not supported.
The column names are simply the object’s attribute names. You must use the names defined for the attributes at the object level—do not use the actual DBMS column names. String values must be quoted as in the previous example. CA SDM data types, such as date and duration, are treated as integers, as shown in the following example:
creation_date > 38473489389
Dot-notation is allowed in the where clause to search through SREL (foreign key) types. For example, the following query against the Request (‘cr’) object, returns all Requests assigned to contacts with a specific last name, as illustrated by the following example:
assignee.last_name like ‘Martin%’
Dot-notation is a great convenience to forming where clauses, but you must be careful to ensure the query is an efficient one. The query in the example could prove inefficient if the contact’s last_name attribute was not indexed in the DBMS (however, for this example, it is). To ensure indexes are used to their best advantage when searching through SRELs, make use of the id attributes of the CA SDM objects. All tables in CA SDM have an index on the id attribute.
The id attribute of an object is easily obtained from the object’s handle. As previously outlined, an object’s handle is a string of the form “<objectName>:<id>”, where id is the value of the id attribute found in every CA SDM object. Simply extract the id portion and use “<attributeName>.id” in the where clause.
An object’s id is either of type integer or a UUID. If it is an integer, simply use it as such. For example, to search for Requests with the rootcause pointing to a Root Cause object with handle, “rc:1234”, the where clause is:
rootcause.id = 1234
If the id of an object is a UUID type, you must format it as follows:
U’<uuid>’
The string representation of a uuid is surrounded by single quotes and prefixed with a capital ‘U’ character. The string representation of a UUID value is the <id> part of an object handle.
Using the previous example, if you know that the handle for a particular contact is “cnt: 913B485771E1B347968E530276916387”, you can form the query, as shown by the following example:
assignee.id = U’913B485771E1B347968E530276916387’
Do not form where clauses querying the ‘persistent_id’ attribute, as in the following example:
rootcause.persistent_id = ‘rc:1234’
For more information about handles, see Out-of-the-Box Handles.
|
Copyright © 2013 CA.
All rights reserved.
|
|