Querying with the OQL syntax
You can use the essential Orchestration Query Language(OQL) keywords and syntax to effectively write queries.
You can monitor the HCL Workload Automation production plan environment by using the OQL, which applies to REST API V2. With the OQL syntax, you can query for items in your database. Also with the OQL syntax, you can retrieve what you need quickly and easily.
The OQL querying begins with an expression, which is composed of different conditions, known as queryTerms. A queryTerm is the minimum condition of a OQL construction, for example job_name = test1.
QueryTerms include three main elements: field, comparison_operator, and value. The fields of the query are case insensitive. The values are case-sensitive.
The field must begin with a letter.
After the first letter, the field can contain the following characters:
- 0 - 9
- a - z
- A - Z
- _
- -
- .
- Integers, both positive and negative.
- Strings in single quotation marks (').
The following table lists OQL keywords with a description.
Keywords | Description |
---|---|
AND | Returns the results that satisfy all the conditions separated by AND. It is case insensitive. |
OR | Returns the results that satisfy at least one of the conditions separated by OR. It is case insensitive. |
( | Opens a priority expression. |
) | Closes a priority expression. |
[ | Opens a list. |
] | Closes a list. |
= | Returns all the elements equal to the specified element. Numbers, strings, or one of these keywords can follow: true, false, or null. |
!= | Returns all the elements different from the given one. Numbers, strings, or one of these keywords can follow: true, false, or null. |
<= | Returns all the elements that are less than or equal to the specified one. Only numbers or strings can follow. |
>= | Returns all the elements that are greater than or equal to the specified one. Only numbers or strings can follow. |
< | Returns all the elements that are less than the specified one. Only numbers or strings can follow. |
> | Returns all the elements that are greater than the specified one. Only numbers or strings can follow. |
IN | Returns items that the specified list includes. |
NOT IN | Returns items that the specified list does not include. |
LIKE | Returns elements that match the specified pattern. The accepted
characters are as follows:
|
NOT LIKE | Returns elements that do not match the specified pattern. The
accepted characters are as follows:
|
ORDER BY | Orders the query results according to the specified fields. You
can use the keyword at the end of the construction. By default, the
field is sorted in ascending order. The accepted values are as follows:
|
, | Separates values inside a list. |
. | Browses into items. |
true | Represents the Boolean value true . |
false | Represents the Boolean value false . |
null | Represents the null value. |
- AND or OR
- The following example explains how to find a job stream that does not have
the name accounting and that contains from 5 to 10
jobs. If this condition is not met, the query searches for a job stream that
contains a job specified in the following list: ['job1',
'job\'3\'',
'job4'].
(jobStreamName != 'accounting' AND (totalJobs > 5 AND totalJobs <= 10)) OR jobName IN ['job1', 'job\'3\'', 'job4']
- LIKE
- The following example explains how to find all the job streams with names
that start with
test1:
jobStreamName LIKE 'test1@
- IN or NOT IN
- The following example explains how to find a job stream named
accounting. If this condition is not met, the query
searches for a job stream that contains a job in the specified list and that
is not on the workstations specified in the other
list:
JobStreamName = 'accounting' OR jobName IN ['italy','united_kingdom'] AND workstation NOT IN ['wks2', 'wks5']
- ORDER BY
- For example, to order field1 in ascending order,
field2 in descending order, and
field3 in ascending order, you can write the query
as
follows:
field1 = 'x' or field2 = 'y' and field3 = 'z' ORDER BY field1, field2 DESC, field3
- Browsing items
- The following example explains how to browse into the item using the period
character ( . ) to retrieve the successors of the job
with the specified ID job_id or of the job stream with
the specified ID
js_id:
(dependencies.type = EXTERNAL_JOB AND dependencies.jobId = <job_id>) OR (dependencies.type = EXTERNAL_JOBSTREAM AND dependencies.jobstreamId = <js_id>)