← IntelliSearch, Advanced Object SearchCustom SQL Filter BNF →

Querying the Model with SQL

Context

The SQL supports a subset of the SQL92 standard.

SQL can be used in the following contexts:

Functions

The following functions have been implemented:

  • AVG([ALL | DISTINCT] expression) - calculates the average.
  • COUNT({ [[ALL | DISTINCT] expression] | *}) - returns the number of items in a group.
  • FORMAT_EXPRESSION(<expression>, <format>[, <timezone>]) - <format> conforms to the SimpleDateFormat date pattern and <expression> to Time.expressionNow() REL function expression.
  • FORMAT_NOW(<format>[, <timezone>]) - <format> conforms to the SimpleDateFormat date pattern.
  • MAX([ALL | DISTINCT] <expression>) - calculate the maximum value from <expression>.
  • MIN([ALL | DISTINCT] <expression>) - calculate the minimum value from <expression>.
  • NOW([<expression>][, <time_zone>]) - <expression> conforms to Time.expressionNow() REL function expression.
  • SUM([ALL | DISTINCT] <expression> ) - returns a sum.
note

Time zones supported are Olson time zones. Navigate to Environment > Time Zones to see the list of supported time zones.

See Extended Time Functions for more information on syntax.

note

Depending on the underlying database, these functions may not always return the expected value. Please see the documentation of your database vendor for implementation specifications.

note

The query results are ordered by UniqueId; this cannot be changed.

Example

Credentials

You want to extend an object filter on credentials to list only credentials with the soap CredentialProtocol

Credential.CredentialProtocol in (select CredentialProtocol.UniqueId from CredentialProtocol where CredentialProtocol.Name = 'soap')

This will create the following query:

SELECT Credential.*, Credential.UniqueId as c_1 FROM Credential WHERE ((Credential.CredentialProtocol in (select CredentialProtocol.UniqueId
from CredentialProtocol where CredentialProtocol.Name = 'soap'))) ORDER BY c_1 ASC

Time Functions

Time functions display the time with a optional time zone; you can apply expressions to alter the returned date and/or time and format the output using date formatting.

FORMAT_EXPRESSION

Formats current time according to a time expression and a SimpleDateFormat, optionally with a time zone.

FORMAT_EXPRESSION('truncate hour', 'yyyy/MM/dd hh:mm:ss', 'Europe/Paris') returns 2023/10/02 04:00:00

FORMAT_EXPRESSION('truncate hour', 'yyyy/MM/dd hh:mm:ss') returns 2023/10/02 03:00:00

FORMAT_NOW

Formats current time according to a SimpleDateFormat, optionally with a time zone.

FORMAT_NOW('yyyyMMdd', 'Europe/Paris') returns 20231002

NOW

Returns current time, optionally with a time zone.

Display processes that have a requested start time between 11:00 in morning today or yesterday and 11:00 tomorrow morning or today, depending on what time it currently is.

Job.RequestedStartTimeInternal > now('subtract 11 hours set hour 11 truncate hour')
and Job.RequestedStartTimeInternal < now('add 13 hours set hour 11 truncate hour')

This will create the following query:

SELECT Job.*, Job.UniqueId as c_1 FROM Job
WHERE (Job.RequestedStartTimeInternal > now('subtract 11 hours set hour 11 truncate hour')
and Job.RequestedStartTimeInternal < now('add 13 hours set hour 11 truncate hour') ORDER BY c_1 ASC

The same expression with a timezone of Europe/Paris:

Job.RequestedStartTimeInternal > now('subtract 11 hours set hour 11 truncate hour', 'Europe/Paris')
and Job.RequestedStartTimeInternal < now('add 13 hours set hour 11 truncate hour', 'Europe/Paris')

This will create the following query:

SELECT Job.*, Job.UniqueId as c_1 FROM Job
WHERE (Job.RequestedStartTimeInternal > now('subtract 11 hours set hour 11 truncate hour', 'Europe/Paris')
and Job.RequestedStartTimeInternal < now('add 13 hours set hour 11 truncate hour', 'Europe/Paris') ORDER BY c_1 ASC

Generic Functions

AVG

AVG is used the calculate an average, in this example an average runtime with a process filter, here: long runners of the last 7 days

Job.RunTime > (select avg(Job.RunTime) from Job
where Job.RequestedStartTimeInternal > now('set hour 0 subtract 7 days'))
and Job.RequestedStartTimeInternal > now('set hour 0 subtract 7 days')

SELECT Job.*, Job.UniqueId as c_1 FROM Job
WHERE Job.RunTime > (select avg(Job.RunTime) from Job
    where Job.RequestedStartTimeInternal > now('set hour 0 subtract 7 days'))
and Job.RequestedStartTimeInternal > now('set hour 0 subtract 7 days') ORDER BY c_1 ASC

MIN, MAX, COUNT

MIN is used to return the minimum, MAX to return the maximum value; in this example, we use MIN, MAX, and AVG on runtime for a report of processes from definitions containing RS in the name, also displays the number of process runs for each definition, so you have an idea of the reliability of the average runtime value.

select j.JobDefinition as JD,AVG(j.RunTime) as AVG_RunTime,MIN(j.RunTime) as MIN_RunTime,
MAX(j.RunTime) as MAX_RunTime, count(distinct j.JobId)
from Job j, JobDefinition jd
where jd.Name like '%RS%' and j.JobDefinition = jd.UniqueId
group by j.JobDefinition,jd.UniqueId

Writing your own Custom SQL

To write your own custom SQL, you need to inspect the data model available in the API documentation. In this example, you query the Credential table and you want filter by credential protocol; in the datamodel, you see the CredentialProtocol field of the Credential table is of type BIGINT which means that it will contain the UniqueID of the credential protocol. You thus have to select the UniqueID of the credential protocol from the CredentialProtocol table.

See Also

SQL

← IntelliSearch, Advanced Object SearchCustom SQL Filter BNF →