Where Clauses

This article contains the following topics:
casm1401
This article contains the following topics:
Several Web Services methods, such as doSelect() and doQuery(), requir
e the Where clauses
for searching by CA SDM and Knowledge Management. 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:
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:
  • Logical operators AND, OR, and NOT
  • LIKE and IS
  • NULL
  • IN
  • Wildcard characters ‘%’ and ‘_’ for string matches
  • All comparison operators: <, >, <=, >=, !=, <>
Parenthesis is used for grouping. Explicit joins, EXISTS, and GROUP BY elements are not supported by CA SDM. String value must be enclosed in quotes, for example, ‘Jones’.
The column names denote the object attribute names. CA SDM data types,data and duration, are treated as integers. For example:
creation_date > 38473489389
You must use the attribute names at the object Level. Do not use the actual DBMS column names.
 
Dot-notation is allowed in the Where clause to search through SREL (foreign key) types. For example, a 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 very helpful in forming the where clauses, but you must ensure that the query is an efficient one. The query in the example
assignee.last_name like 'Martin%
' can be inefficient if the contact’s last_name attribute is not indexed in the DBMS. 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 can be easily obtained from the object’s handle. 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. Extract the ID portion and use “<attributeName>.id” in the Where clause.
An object’s ID is either an 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 attribute of an object is a UUID type, you must format it as:
U'<uuid>'
The string representation of a UUID is enclosed in single quotes and prefixed with capital ‘U’. This string is the <id> part of an object handle. For example, if you know that the handle for a particular contact is cnt:913B485771E1B347968E530276916387, you can form the query as:
assignee.id = U'913B485771E1B347968E530276916387'
Do not form the Where clauses by querying the ‘persistent_id’ attribute, as in the following example:
rootcause.persistent_id = 'rc:1234'
For more information about handles, see Default Handles.
IN Clause
The IN clause requires some special explanation. The two syntactic forms are:
SREL_attr_name.subq_WHERE_attr[.attr] IN ( value1 [, value2 [,...]] ) SREL_attr_name.[subq_SELECT_attr]LIST_name.subq_WHERE_attr IN (value1, [,value2 [,...]] )
The left side of the clause must begin with an SREL-type attribute of the table being queried, which is represented by
SREL_attr_name
.
subq_WHERE_attr
is an attribute of the foreign object, which itself may be another SREL pointer.
For example, a query against the request (‘cr’) object may be coded as follows:
category.sym IN ('Soft%', 'Email')
This translates to the following pseudo-SQL:
SELECT ... FROM cr WHERE cr.category IN (SELECT persistent_id FROM pcat WHERE sym LIKE 'Soft%' OR sym = 'Email')
In the previous sub query , ‘pcat’ is the object name pointed to by cr.category.
The second form of the IN clause can search through BREL lists. For example, to find all requests assigned to an analyst in a specific group, the clause is as follows:
assignee.[member]group_list.group IN (U'913B485771E1B347968E530276916387')
The first part of the clause, assignee, is an SREL (foreign key) of the cr object, pointing to the cnt object. Next, group_list, which is an attribute of the cnt object, is a list of cnt objects that represent groups to which a contact belongs. The last part, group, forms the first part of the where clause for the IN sub query. ‘U’913B485771E1B347968E530276916387is the foreign key value to match on group. The sub query return is specified by [member]. This translates to the following pseudo-SQL statement:
SELECT ... FROM cr WHERE cr.assignee IN (SELECT member from grpmem WHERE group = U'913B485771E1B347968E530276916387')
You can specify multiple foreign keys for matching multiple objects by providing a comma-separated list:
assignee.[member]group_list.group IN (U'913B485771E1B347968E530276916387', U'913B485771E1B347968E530276916300')
You cannot extend the dot notation for this use of the IN clause, for example, the following is not valid:
assignee.[member]group_list.group.last_name IN ('Account Center')
One use of IN is to avoid Cartesian products. For example, the following query results in a Cartesian product and is very inefficient:
assignee.last_name LIKE 'MIS%' OR group.last_name LIKE 'MIS%'
Using IN, the query can be coded as follows:
assignee.last_name IN 'MIS%' OR group.last_name IN 'MIS%'
This query does not create a Cartesian product; in fact, it creates no joins at all.
The parentheses that normally enclose the list of values on the right side of IN can be omitted if there is only one value in the list. Similarly, you should avoid joins by converting queries.
assignee.last_name LIKE 'Smith'
to:
assignee = U'913B485771E1B347968E530276916387'
This avoids the join with some loss in clarity. Using IN, the same partition can be written as follows, with the clarity of the first version and almost the same efficiency as the second version:
assignee.last_name IN 'Smith'
The ‘NOT’ keyword cannot be in conjunction with IN, for example, “NOT IN”.
Lists
Some Web Services methods return
lists
, represented by a unique integer handle. A list is simply a collection of same-type objects. Lists are especially useful when dealing with a large collection of objects (for example, all the contacts in the system) because you can retrieve information about items in a range of the list. The disadvantage is that you must make more method calls to obtain a list handle, retrieve information, and finally, free the list handle. If the expected number of list rows is small, use methods that do not involve list handles, such as doSelect().
The following describes more details about lists:
  • Lists are homogenous
    List may only contain objects of a single type, for example, lists of contacts, list of organizations, and so on.
  • Lists are Static
    For example, if a list object is obtained for all contacts and another contact is added to the system, the update is not reflected in the list. Another list handle must be obtained to get the most current data.
  • List Handles
    A request for a list returns an integer handle representing the list of same-type objects. No other information is sent to the client. The client may query the list for specific information about its rows. When a client is finished with a list, the handle must be released with freeListHandles(). The CA SDM server maintains the list, consuming system resources. Therefore, it is important to free lists. Unlike object handles, list handles are not persistent across sessions.
  • Integer Index
    Several methods require an integer index into a list. Lists are zero-based so the first element is at index = 0.
As previously mentioned, using list handles is most useful for larger sets of data that may be queried multiple times. For some operations, however, lists are excessive. Several methods are provided, but the most notable is doSelect(), as it returns requested information about a set of data without the overhead of list handles.
The decision to use list handles versus methods, such as doSelect(), is one of performance and convenience. For example, suppose your application does processing on all 15,000 Contacts in your system. The doSelect() method can retrieve all the contact data in one call, but the reply will be delayed and will negatively impact overall system performance while it assembles and returns a very large data set. The doQuery() method, in this case, will return a list reference very quickly. Ranges of data can be queried from the list to improve response times from the server. A good practice to follow is to use list references if the data set exceeds 250 items.
Sometimes it does not make sense to use list handles. For example, an issue has a list of Activity Logs. Depending on the installation, the number of logs can range from a few to several dozen. It is probably faster to request the data all at once instead of requesting a list reference, querying it for data, and then releasing the list.
Examples of methods that return data sets instead of list references include the following:
  • doSelect()
  • getRelatedListValues()
  • getLrelValues()
  • getTaskListValues()
  • getValidTaskTransitions()
As previously stated, queries that return a large number of rows can severely impact the performance of the server. To protect against this, CA SDM limits the number of rows returned to 250. This affects all CA SDM Web Services methods that return lists of objects, including the following:
  • doSelect()
  • doSelectKD()
  • getGroupMemberListValues()
  • getListValues()
  • getPropertyInfoForCategory()
  • getRelatedListValues()
  • getTaskListValues()
  • getValidTaskTransitions()
This limit applies even if you request one of these methods to retrieve more than 250 rows.
To retrieve large numbers of rows, you should obtain a handle to the list of results and use getListValues() to retrieve chunks of 250 or fewer rows each. This strategy helps keep the server from becoming slow while serving huge amounts of data.