Query Work List Using SQL

API Type

REST

HTTP Method

POST

Description

Retrieves a list of manual work items based on specified query expression.

URL Format (On Premises)

https://[domain]:[port]/AgilePointServer/Workflow/QueryWorkListUsingSQL

URL Format (AgilePoint for Azure)

https://[domain]:[port]/AgilePointService/Workflow/QueryWorkListUsingSQL

Request Parameters

Name	Description
None	Not Applicable

Request Body Properties

Name	Description
sqlWhereClause	Definition: The where clause of the SQL statement you want to query. Type string Allowed Values: The where clause of a SQL statement.

JavaScript Code Example

$("#btnQueryWorkListUsingSQL").click(function () {
  
  var JSONObject = { 
    sqlWhereClause: "WF_MANUAL_WORKITEMS.USER_ID='demo3\\lilly.allen' 
                             and WF_MANUAL_WORKITEMS.STATUS 
                             in ('New', 'Assigned') " 
  };
  $.ajax({
    url: "https://mydomain:9011/AgilePointServer/Workflow/QueryWorkListUsingSQL",
    data: JSON.stringify(JSONObject)
  });
  
});

Java Code Example

public String QueryWorkListUsingSQL(String sqlWhereClause) {

  String URI = "http://mydomain:9011/AgilePointServer/Workflow/QueryWorkListUsingSQL";

  HTTPOperations ops = new HTTPOperations(domain, this.userName,
                       password, appID, locale);

  JSONObject postData = new JSONObject();
  try {
    postData.put("sqlWhereClause", sqlWhereClause);

    return ops.POSTMethod(URI, postData.toString());
  } catch (JSONException e1) {
    e1.printStackTrace();
  }
  return "";
}

C# Code Example

public string QueryWorkListUsingSQL()
  {
    string URI = "http://mydomain:9011/AgilePointServer/Workflow/QueryWorkListUsingSQL";

    string jsonRequestData = "{
      \"sqlWhereClause\":\"WF_MANUAL_WORKITEMS.USER_ID='MYDOMAIN\\\\administrator' and 
      WF_MANUAL_WORKITEMS.STATUS in ('New', 'Overdue') \"}";
           
    HTTPOperations ops = new HTTPOperations(domain, this.userName, 
                         password, appID, locale);

    return ops.POSTMethod(URI, jsonRequestData);
  }

Output

A list of work items which match the where condition.

JSON Response Body Example

[{
  "ActivatedDate": "\/Date(928149600000+0000)\/",
  "ActivityInstID": "249CCC6F0B48470B8668BF4567B67C75",
  "ApplName": "MyApplication",
  "AssignedDate": "\/Date(1390852380123+0000)\/",
  "BeingProcessed": false,
  "CancelledDate": "\/Date(928149600000+0000)\/",
  "ClientData": "",
  "CompletedDate": "\/Date(928149600000+0000)\/",
  "CreatedDate": "\/Date(1390852380123+0000)\/",
  "DefName": "BudgetRequest",
  "DisplayName": "Waiting for Patient to perform Intake",
  "DueDate": "\/Date(1390938780000+0000)\/",
  "DueHandled": false,
  "LastModifiedBy": "demo3\\lilly.allen",
  "LastModifiedDate": "\/Date(1390852390623+0000)\/",
  "Name": "Intake",
  "OriginalUserID": "demo3\\lilly.allen",
  "Pending": true,
  "PoolID": "",
  "PoolInfo": "",
  "Priority": "Normal",
  "ProcDefID": "EEA1DE90DC3144E3A0D8B9487BBE44D4",
  "ProcInstID": "A8D80F3FEB6C4E74A4D001036E30E5D6",
  "ProcInstName": "BudgetRequest-1\/27\/2014 11:49:07 AM",
  "ResolveParticipant": "",
  "RestrictionType": "  ",
  "Session": 2,
  "SourceWorkItemID": "",
  "Status": "Assigned",
  "UserID": "demo3\\lilly.allen",
  "WaitWorkPerformed": true,
  "WorkItemID": "287524CC39B34DC3BDD73275BA7769DE",
  "WorkObjectID": "459BCC8A8A36444DAA7077CF0F6C1254"
}]

Supported Versions

AgilePoint BPMS v5.0 R2 and higher