ARGetListEntryWithMultiSchemaFields

Description

Performs dynamic joins by querying across multiple forms (including view and vendor forms) at run time. Depending on the structure of your data, this function might also perform the following suboperations to support dynamic joins:

• Recursive queries — If a form contains hierarchical (parent-child) data, such as manager and employee relationships, this function can use one query to search the form recursively and retrieve the complete hierarchy and related attributes. See Recursive queries.
• Value set queries — This function can perform IN and NOT IN operations on a fixed value set and on a value set returned by a subquery. See Value set queries.
• Aggregate functions, group by clause, and having clause — To refine the selection of data retrieved from the database and reduce the need for in-memory processing of large numbers of records, you can now apply aggregate functions along with group by and having clauses. (AR System releases prior to 7.6.02 do not support these options.)
• Vendor form joins — Joining vendor forms with other types of forms enables a single query to access data in both BMC Remedy AR System and external sources. See Using aggregate functions, groupBy clauses, and having clauses.

Data in each record is returned as multischema field/value pairs. You can limit the list to entries that match particular conditions by specifying the qualifier parameter.

For detailed structural information, see these documents:

• (C API) Structures for ARGetListEntryWithMultiSchemaFields
• (Java API) Javadoc comments

For examples of how to implement this function, see Retrieving entries from multiple forms.

Privileges

The system returns information based on the access privileges of the user that you specify for the control parameter. All lists, therefore, are limited to entries that the user can access (users must have permission for the entryId field to access and retrieve entries).

Synopsis

#include "ar.h"
#include "arerrno.h"
#include "arextern.h"
#include "arstruct.h"

int ARGetListEntryWithMultiSchemaFields(
   ARControlStruct *control,
   ARMultiSchemaFuncQueryFromList *queryFromList,
   ARMultiSchemaFieldFuncList *getListFields,
   ARMultiSchemaQualifierStruct *qualifier,
   ARMultiSchemaSortList *sortList,
   unsigned int firstRetrieve,
   unsigned int maxRetrieve,
   ARBoolean useLocale,
   ARMultiSchemaFieldIdList *groupBy,
   ARMultiSchemaFuncQualifierStruct *having,
   ARMultiSchemaFieldFuncValueListList *entryList,
   unsigned int *numMatches,
   ARStatusList *status)

Input arguments

control

The control record for the operation. It contains information about the user requesting the operation, where to perform the operation, and which session is used to perform it. The user and server fields are required.

queryFromList

A list of items to query and the join conditions between each item. An item can be a form (schema) or a recursive query. This parameter is used to generate the query's FROM clause.

All types of forms except display-only can be joined.

Only one recursive query can be included in the list, and it must be the first item in the list. The recursive query can operate on only one form in the list. (See ARMultiSchemaRecursiveFuncQueryStruct.)

Nested queries are not supported.

You can use inner, left outer, and right outer joins. If join information between objects in the list is not specified, the result set is a Cartesian product.

For more information, see ARMultiSchemaFuncQueryFromList.

getListFields

A list of zero or more fields to retrieve for each entry from the forms listed in the queryFromList parameter, along with the type of aggregate, if any. This parameter is used to generate the query's SELECT statement.

If no fields are specified, the fields in the Results List Fields property of each form in the queryFromList parameter are returned. If that property is empty, the form's Short Description field is returned.

The system checks the permissions for each field and returns only those fields for which the user has read access.

Because ARGetListEntryWithMultiSchemaFields can operate on multiple forms, each field in this list must be qualified by a form name or alias. Additionally, each field in this list must specify the aggregate function being applied to the field.

getListFields supports the following aggregate functions:

• NONE
• SUM
• AVG
• COUNT
• MIN
• MAX

See ARMultiSchemaFieldFuncStruct, ARMultiSchemaFieldFuncList, and ARMultiSchemaFuncQualifierStruct.

qualifier

Zero or more conditions that limit the set of entries that this function retrieves. This parameter is used to generate the query's WHERE clause. (For some databases, the join qualifiers are also used to generate the WHERE clause.)

A qualification can include one or more fields and any combination of conditional, relational, arithmetic (numeric data types only), and IN or NOT IN operations. IN and NOT IN operations can be performed on a fixed value set or on a value set returned by a subquery.

The system generates an error if the user does not have read permission for a field or if a field does not exist. To retrieve all entries, set this parameter to NULL or set its operation element to 0 (AR_COND_OP_NONE).

See ARMultiSchemaQualifierStruct.

sortList

A list of zero or more fields that specifies the entry sort order. This parameter is used to generate the primary query's ORDER BY clause. This parameter cannot be used in subqueries.

If no field is specified, the system sorts the entries according to the first form's default sort order. The system generates an error if you do not have read access to all specified fields.

See ARMultiSchemaSortList.

firstRetrieve

The first entry to retrieve. A value of 0 (AR_START_WITH_FIRST_ENTRY) represents the first entry.  A value of 1 will skip the first entry. 

maxRetrieve

The maximum number of entries to retrieve. Use this parameter to limit the amount of data returned if the qualification does not sufficiently narrow the list. To assign no maximum, specify 0 (AR_NO_MAX_LIST_RETRIEVE).

useLocale

A flag that indicates whether to search for entries based on the locale. If you specify 1 (TRUE) and the Localize Server option is selected, entries are searched using the locale specified in AR_RESERV_LOCALE_LOCALIZED_SCHEMA. If no matches are found for the specified locale, the search becomes less restrictive until a match is found. If you specify 0 (FALSE) or the Localize Server option is cleared, all entries are searched. For more information, see Setting the Localize Server option.

groupBy

A list of zero or more fields used to partition the result set. This parameter is used to generate the primary query's GROUP BY clause. This field can be null. If no fields are specified, no GROUP BY clause is generated.

A common use for GROUP BY is to create partitions in the result set over which the database server computes an aggregate function. For example, consider a table of ticket IDs, ticket statuses, and agent names. A SQL query to discover for each agent how many tickets are open would look like this:

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent

The following rules apply to the groupBy parameter:

• If groupBy is specified, all fields in the select list must be aggregates or included in the groupBy list.
• Aggregate fields and sub-queries (selects) might not appear in the groupBy list.
• Fields might appear in the groupBy list that are not in the select list.
• Fields used in the groupBy list do not have to be in the having list and fields used in the having list do not need to be in the groupBy list.

having

Used to generate the primary query's HAVING clause. This field can be null. If null or an empty qualifier is specified, no HAVING clause is generated.

The HAVING clause filters the result set. Unlike the qualifier, it can specify aggregate functions. For example, to modify the example query used to illustrate the groupBy clause to further limit the result list to agents with more than 10 tickets open, the query would look like this:

SELECT COUNT(ticketId),agent FROM tickets WHERE status='Open' GROUP BY agent HAVING COUNT(ticketId) > 10

The following rules apply to the having parameter:

• Numeric or enumerated fields must be used for any function other than COUNT.
• Aggregate and non-aggregate fields can be in the having qualifier.
• Fields might appear in the having qualifier that are not in the select list.
• Fields used in the groupBy list do not have to be in the having clause and fields used in the having clause do not need to be in the groupBy list.

Return values

entryList

A list of zero or more (accessible) entries that match the criteria specified in the qualifier parameter. The system returns an ARMultiSchemaFieldFuncValueListList, in which each field contains the queryAlias, fieldId, and funcID. The list contains zero items if no entries match the specified criteria.

numMatches

The total number of (accessible) entries that match the qualification criteria. This value does not represent the number of entries returned unless the number of matching entries is less than or equal to the maxRetrieve value. If you do not want to retrieve this value, set this parameter to NULL.

status

A list of zero or more notes, warnings, or errors generated from a call to this function. For a description of all possible values, see Error checking.