Run a Data Service Query
Fetch table, view, and synonym data from the application database by sending POST requests to the Runquery endpoint. The content type of the POST request must be application/json.
The following example illustrates a valid JSON object that may be used with the data service to gather activity data:
Example Request Data 1
{ "name": "My Activities", "sinceDate": "2001-11-15 14:27:49 -0500", "tables": [ { "tableName": "TASK", "columns": [ "TASK_ID", "TASK_NAME", "TASK_TYPE", "STATUS_CODE", "ACT_START_DATE", "ACT_END_DATE" ], "condition": { "operator": "AND", "conditions": [ { "columnName": "REMAIN_WORK_QTY", "operator": "GREATER_THAN_OR_EQUALS", "value1": "116" }, { "columnName": "PROJ_ID", "operator": "EQUALS", "value1": "4603" } ] } } ] }
Example Request Data 2
{ "name": "My Activities", "sinceDate": "2001-11-15 14:27:49 -0500", "tables": [ { "tableName": "UNIFIER_BUDGETCELL_VIEW", "columns": [ "ITEM", "US_VALUE", "FIELDTYPENAME", "FIELDTYPEID", "X", "BFIELDNAME", "CODE", "VALUE", "Y", "FIELDTOTALMODE", "FIELDDISPLAYMODE", "BITEMID", "BFIELDID", "BUDGETID", "ITEMTYPEID", "FIELDFORMAT" ], "orderByColumns": [ "BUDGETID", "ITEMTYPEID", "FIELDTYPEID" ], "condition": { "operator": "AND", "conditions": [ { "columnName": "BUDGETID", "operator": "EQUALS", "value1": "14" } ] } } ] }
The previous example JSON object contains several properties that specify the parameters of the query processed by the data service. The following list provides further information on important properties:
- sinceDate: Restricts results to data that has been modified on or after the date passed as a value to this property. If this property is set to null, data will not be filtered by modification date. See: Using sinceDate This date comes from the response from previous request. The response contains a safetyDate section:
"safetyDate": [ { "queryName": "My Activities", "sinceDate": "2001-11-15 14:27:49 -05:00" }
You can copy the returned sinceDate value to next request if you are running incremental queries. The next query only returns rows that changed on or after this date, plus the filtering in the request.
- tables: Contains one or more objects specifying the application database tables to include in the query. Upon processing the request, the data service will only return data from the included tables.
- columns: Lists one or more database table columns. The data service will only return data from the specified table columns. Other table data will not be included in the response.
- conditions: Specifies a set of conditions column data must meet in order to be included in the response. In the prior example, the second condition object in the conditions property array ensures only data associated with project D 4603 will be returned by the data service.
- orderByColumns: Lists one or more database table columns combination as a unique row. The data service will sort based on these columns in the same provided order for "views" and "synonyms". Currently, it is not supported for "tables".
Null Values
You can pass a value of null to request JSON object's properties. Properties with null values will not factor into the processing of the query. For example, submitting a query object with a sinceDate property value of null will ensure application data is returned by the data service regardless of its last modification date.
For more information on the properties of the JSON query object, see the documentation for the data service runquery endpoint.