SQL Query Functions

RunMyJobs supports the following SQL functions.

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

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

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

See Extended Time Functions for more information on syntax.

Examples

AVG

AVG is used the calculate an average. This query filter example returns long-running processes from the past seven days with a process filter.

Copy 
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')

This will create the following SQL query:

Copy 
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

In this example, we use MIN, MAX, and AVG on runtime for a report of processes from definitions containing RS in the name. This query also displays the number of process runs for each definition, so you have an idea of the reliability of the average runtime value.

Copy 
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

FORMAT_EXPRESSION

This function formats the 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/07/27 04:00:00

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

FORMAT_NOW

This function formats the current time according to a SimpleDateFormat, optionally with a time zone.

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

NOW

This function returns the current time, optionally with a time zone.

This example displays 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.

Copy 
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 SQL query:

Copy 
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

Here is the same query with a timezone of Europe/Paris:

Copy 
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:

Copy 
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