Interface | Description |
---|---|
IPage |
Interface that provides the ability to consume a result set using pages.
|
IQuery |
An object that represents a query to execute.
|
IResultSetRow |
Interface that provides the ability to consume a row of fields.
|
ITabularResultSet |
Interface that provides the ability to consume a result set using rows.
|
Class | Description |
---|---|
ParameterValue |
An object that represents an input binding parameter for a parameterized SQL statement.
|
Supports interfaces and classes for the OpenPages Query Service.
The OpenPages Query Service provides a type-based query service for discovering objects that match specified criteria, by projecting the OpenPages Domain model as a relational view. Through this relational view, queries may be performed using a simplified SQL SELECT statement.
The query language is based on a subset of the SQL-92 grammar with extensions to enhance its filtering capability for the Domain model, such as enumeration-valued/multi enumeration-valued fields, full-text search, and hierarchal traversal.
The semantics of this query language is defined by the SQL-92 standard, including the extensions, in conjunction with the Domain model relational view. The basic structure of an OpenPages query is an SQL statement that must include the following clauses:
query : SELECT select_list from_clause where_clause? group_by_clause? order_by_clause? select_list : select_sublist (COMMA select_sublist)* | STAR select_sublist : column_reference | qualifier DOT STAR qualifier : one_table column_reference : column_name | qualifier DOT column_name | COUNT LPAR column_name RPAR | COUNT LPAR qualifier DOT column_name RPAR | COUNT LPAR qualifier DOT STAR RPAR | COUNT LPAR STAR RPAR from_clause : FROM table_reference table_join : JOIN one_table join_specification join_specification : ON join_predicate LPAR one_table RPAR | ON join_predicate LPAR one_table COMMA level RPAR join_predicate : CHILD | PARENT | ANCESTOR table_reference : one_table table_join* one_table : table_name | table_name AS correlation_name correlation_name : ENTITY table_name : ENTITY column_name : ENTITY where_clause : WHERE search_condition search_condition : sub_search_condition ((AND^ | OR^) sub_search_condition)* sub_search_condition : predicate | LPAR search_condition RPAR predicate : comparison_predicate | like_predicate | null_predicate | text_search_predicate | in_predicate text_search_predicate : CONTAINS LPAR value_expression COMMA string_literal RPAR | NOTCONTAINS LPAR value_expression COMMA string_literal RPAR comparison_predicate : value_expression comparison_op literal comparison_op : EQ | NEQ | LT | GT | LTEQ | GTEQ like_predicate : value_expression LIKE string_literal | value_expression NOTLIKE string_literal in_predicate : value_expression IN LPAR literal_list RPAR | value_expression NOTIN LPAR literal_list RPAR null_predicate : value_expression ISNULL | value_expression ISNOTNULL literal_list : literal (COMMA literal)* value_expression : column_reference literal : num_literal | string_literal | date_literal | boolean_literal string_literal : QUOTED_STRING date_literal : DATE QUOTED_STRING boolean_literal : TRUE | FALSE level : INTEGER_LIT num_literal : INTEGER_LIT | DECIMAL_LIT group_by_clause: GROUP BY group_by_specification (COMMA group_by_specification)* group_by_specification: column_name | qualifier DOT column_name order_by_clause : ORDER BY sort_specification (COMMA sort_specification)* sort_specification : column_reference | column_reference (ASC | DESC)
Field Name | Type | Description |
---|---|---|
Resource ID | ID | |
Comment | String | |
Created By | Integer | |
Creation Date | Date | |
Description | String | |
Last Modification Date | Date | |
Last Modified By | Integer | |
Location | String | |
Name | String | |
Orphan | String | Possible values are: Not Orphan Direct Orphan Indirect Orphan |
Is Locked | Boolean |
SELECT COUNT(*) FROM [SOXIssue]Example: Get the number of instances by each [OPSS-Issue:Conclusion] in the object type [SOXIssue].
SELECT [OPSS-Issue:Conclusion], COUNT([OPSS-Issue:Conclusion]) FROM [SOXIssue] GROUP BY [OPSS-Issue:Conclusion]
SELECT [SOXBusEntity].[Name], [LevelOne].[Name], [LevelTwo].[Name] FROM [SOXBusEntity] JOIN [LevelOne] ON PARENT([SOXBusEntity]) JOIN [LevelTwo] ON PARENT([LevelOne])
SELECT [SOXBusEntity].[Name], [LevelOne].[Name], [LevelTwo].[Name] FROM [LevelTwo] JOIN [LevelOne] ON CHILD([LevelTwo]) JOIN [SOXBusEntity] ON CHILD([LevelOne])
JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity]) is equals to JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity], 0)For the purpose of all the examples we have the following SOXBusEntity object instances.
entity3 | ---- entity2 | ---- entity1 | | ---- entity0Example
SELECT [SOXBusEntity].[Name], [a].[Name] FROM [SOXBusEntity] JOIN [SOXBusEntity] AS [a] ON PARENT([SOXBusEntity]) WHERE [SOXBusEntity].[Name] = 'entity3'
[SOXBusEntity].[Name] | [a].[Name] |
---|---|
entity3 | entity2 |
entity3 | entity1 |
entity3 | entity0 |
Example
A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses
a bottom up traversal and will include all levels in the result set.
SELECT [SOXBusEntity].[Name], [a].[Name] FROM [SOXBusEntity] JOIN [SOXBusEntity] AS [a] ON CHILD([SOXBusEntity]) WHERE [SOXBusEntity].[Name] = 'entity0'
[SOXBusEntity].[Name] | [a].[Name] |
---|---|
entity0 | entity3 |
entity0 | entity2 |
A left outer join is supported in the syntax using OUTER JOIN in place of JOIN. All other behavior of the OUTER JOIN is the same as JOIN
ExampleSELECT [SOXBusEntity].[Name], [LossEvent].[Name] FROM [SOXBusEntity] OUTER JOIN [LossEvent] ON PARENT([SOXBusEntity])
The result of this execution would produce the following result set, with 'entity2' not having any LossEvent children, it is still listed.
[SOXBusEntity].[Name] | [LossEvent].[Name] |
---|---|
entity0 | LE001 |
entity0 | LE002 |
entity1 | LE003 |
entity2 |
Indirect Joins allow you to specify a top-down query that considers all paths down the object hierarchy from a parent to a child type, where the child type in the join is not a direct child association. For example, to find all Issues under a Risk, as Issue may be a child of the Risk directly, but also a child of Controls or Test plans. While it would be possible to do an explicit series of JOINs in the FROM clause from Risk to Issue, or Risk to Control then to Issue, it would not be possible or efficient to do them in one SELECT statement. Indirect join syntax can accomplish this type of query in one SELECT statement with certain restrictions on allowed queries.
The syntax for making an indirect join is the same, other than the join
predicate which is ...ON ANCESTOR(...)
SELECT [SOXIssue].[Name] FROM [SOXRisk] JOIN [SOXIssue] ON ANCESTOR([SOXRisk]) WHERE [SOXRisk].[Resource ID] = 1234
The result of this query would produce a list of Issues that are related via any path to the Risk with Resource ID = 1234.
Restrictions:Indirect usage is restricted to queries that meet the following criteria:
It is always faster to use a direct query join (ON PARENT/CHILD) rather than indirect (ON ANCESTOR) if the query is only returning direct associations.
Due to the variable nature of the OpenPages database schema, the performance of this query will be impacted by a number of factors such as: "depth" of the indirect query (i.e., the number of hierarchy levels the query must search before finding the child type); the total number of associations beneath the starting Ancestor resource; as well as the complexity of security rules on any involved object types. Depending on these factors, queries may perform slower than expected and introduce more load on the database server.
Indirect queries support the usage of paging mechanisms, however due to the expensive nature of determining the indirect associations at the database level, it is more efficient for the database server to use larger page sizes in the query options. There is ultimately a tradeoff to consider, which is that larger page sizes will consume more system memory on the application server when results are returned. One way to restrict the amount of memory consumed by large numbers of result rows is to limit the columns returned to only required information. Please note that fields of Long String datatype can potentially have large amounts of data that would need to be kept in memory if included in the results.
If you only require indirect children through primary associations, leverage
the query option honorPrimary
to reduce the number of
association paths to be evaluated as only primary parent associations are
traversed. This will improve the performance of executing the query for this
use case.
Field Type | Supported Operators | Supported Literal |
---|---|---|
String (single) | =, <>, [NOT] LIKE, IS [NOT] NULL | String |
String (IN) | [NOT] IN | List of Strings |
Integer (single) | =, <>, >, <, >=, IS [NOT] NULL | Integer |
Integer (IN) | [NOT] IN | List of Integers |
Float (single) | =, <>, >, <, >=, <=, [NOT] LIKE, IS [NOT] NULL | Double |
Float (IN) | [NOT] IN | List of Doubles |
Date (single) | =, <>, >, <, >=, IS [NOT] NULL | Date string format |
Date (IN) | [NOT] IN | List of Date string format |
Currency (single) | =, <>, >, <, >=, <=, [NOT] LIKE, IS [NOT] NULL | Double |
Currency (IN) | [NOT] IN | List of Double |
Boolean (single) | =, <>, >, <, >=, <=, IS [NOT] NULL | Boolean |
Enum (single) | =, <>, [NOT] LIKE, IS [NOT] NULL | String |
Enum (IN) | [NOT] IN | List of Strings/Integers |
Multi-Enum(single) | =, <>, [NOT] LIKE, IS [NOT] NULL | String |
Multi-Enum (IN) | [NOT] IN | List of Strings/Integers |
Medium String (single) | [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL | String |
Large String (single) | [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL | String |
Unlimited String (single) | [NOT] CONTAINS, [NOT] LIKE, IS [NOT] NULL | String |
ID (single) | =, <>, [NOT] LIKE | String |
ID (IN) | [NOT] IN | List of Strings |
Example
A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a bottom up traversal and will include all levels in the result set.
SELECT [Shared:Shared Currency] FROM [LevelOne] WHERE [Shared:Shared Currency] = 5.0
Predicate is true if:
x = y (x is equal to y) x <> y (x is not equal to y) x < y (x is less than y) x > y (x is greater than y) x >= y (x is greater than or equal to y) x <= y (x is less than or equal to y)
[Creation Date] IN ('2013-05-16', '2013-05-18')
by enum value name: [Shared:Shared Drop Down] IN ('New York', 'Rhode Island') by enum value id: [Shared:Shared Drop Down] IN (4215, 4218)
A SOXBusEntity type is a parent of a SOXBusEntity type. The syntax expresses a bottom up traversal and will include all levels in the result set.
SELECT [Resource ID], [Name] FROM [SOXBusEntity] WHERE [Name] LIKE '%entity%'
[Name] IS NULL [Name] IS NOT NULL
SELECT [Shared:Shared Drop Down] FROM [LevelOne] WHERE CONTAINS([Shared:Shared Drop Down], 'some text to search for')
The GROUP BY clause is used only when there are aggregate function columns in the SELECT clause.
Example
SELECT [OPSS-Issue:Conclusion], COUNT(*) FROM [SOXIssue] GROUP BY [OPSS-Issue:Conclusion]
Only columns in the SELECT clause MAY be in the ORDER BY clause. Ordering is impacted by collation rules defined by the OpenPages schema.
The following example demonstrates the column name (Resource ID) which is not qualified.
SELECT [Resource ID] FROM [SOXBusEntity]
Example
The following example demonstrates the column name (Resource ID) which is qualified by its type (SOXBusEntity).
SELECT [SOXBusEntity].[Resource ID] FROM [SOXBusEntity]
Example
The following example demonstrates a column name (Resource ID) that is qualified by a correlation name that is defined on the FROM clause (entity).
SELECT [entity].[Resource ID] FROM [SOXBusEntity] AS [entity]
For example:
Example
The following example demonstrates the date string format for comparing a date field to a literal in the WHERE clause:
SELECT [Creation Date] FROM [SOXBusEntity] WHERE [Creation Date] = DATE '2013-05-16'
Context context = ... IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context); IQueryService queryService = serviceFactory.createQueryService(); String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]"; IQuery query = queryService.buildQuery(query); ...
Once a query has been created you may optionally change query options using settings on the IQuery before the query is executed. More information on usage of these parameters is on the IQuery interface java documentation.
IQuery query = queryService.buildQuery(query); query.setHonorPrimary(true); query.setCaseInsensitive(true); query.setMaxRows(100); ...
Context context = ... IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context); IQueryService queryService = serviceFactory.createQueryService(); String query = "SELECT [Resource ID], [Name] FROM [SOXIssue] WHERE [Status] = ?"; ParameterValue param1 = new ParameterValue("Open"); IQuery query = queryService.buildQuery(query); query.bindParameter(1, param1); ...
Context context = ... IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context); IQueryService queryService = serviceFactory.createQueryService(); String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]"; IQuery query = queryService.buildQuery(query); ITabularResultSet resultset = query.fetchRows(0); ...
Context context = ... IServiceFactory serviceFactory = ServiceFactory.getServiceFactory(context); IQueryService queryService = serviceFactory.createQueryService(); String query = "SELECT [Resource ID], [Name] FROM [SOXIssue]"; IQuery query = queryService.buildQuery(query); ITabularResultSet resultset = query.fetchRows(0); for (IResultSetRow row : resultset) { // process rows... for (IField field : row) { // process fields... } ... }
You may add indexes to improve performance of running a query. For information on adding an index, please see section Viewing the Configuration and Settings page > Platform folder settings > Reporting Schema folder settings in the OpenPages GRC Platform Administrator's Guide.
Licensed Materials - Property of IBM
© Copyright IBM Corp. 2013, 2018. All Rights Reserved.
US Government Users Restricted Rights -
Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.