This section provides a collection of sample queries that can be used to retrieve useful information from the database. These queries are designed to help users better understand how to query the database and retrieve the information they need. Please refer to the following list of queries for more information.
- Retrieve Users Belonging to a Specific Group
To retrieve a list of users that belong to a specific group, apply either of the following queries:
- SQL
Select top 100 percent u.Id, u.Title
from ray.MercPrincipals u
join ray.MercPrincipalGroupMembers gp on gp.PrincipalID=u.ID
join ray.MercPrincipals g on g.ID=gp.GroupID
where u.MemberType=2 and u.Active=1 and g.Title='All users'
order by u.Title- HQL
select u FROM User u JOIN u.PrincipalGroups g where g.Title='Auditors'- Retrieve All Active Tasks For All Applications
To retrieve a list of all active tasks for all applications, apply the following query:
select i.Id, ray.DecodeML(i.Subject, 'en') InstanceName, CreationDate, p.Title AssignedTo, t.ApplicationName, ray.DecodeML(t.Subject, 'en') TaskTypeName, pv.FaTitle ProcessName
from ray.BPMS_TaskInstance i
left join ray.MercPrincipals p on i.PrincipalId=p.ID
left join ray.BPMS_TaskType t on i.TaskTypeId=t.Id
left join ray.BPMS_Process pv on t.ProcessId=pv.Id- Retrieve All Closed Tasks For All Applications
To retrieve a list of all closed tasks for all applications, apply the following query:
select i.Id, ray.DecodeML(i.Subject, 'en') InstanceName, CreationDate, i.DoneDate, i.Duration, i.Status, p.Title AssignedTo, u.Title CompletedBy, t.ApplicationName, ray.DecodeML(t.Subject, 'en') TaskTypeName
from ray.BPMS_TaskInstanceCompleted i
left join ray.MercPrincipals p on i.PrincipalId=p.ID
left join ray.MercPrincipals u on i.UserId=u.ID
left join ray.BPMS_TaskType t on i.TaskTypeId=t.Id