Query Tables Data
post
/pds/rest-service/dataservice/runquery
Send a request to this endpoint to query databases registerd with Primavera data service. This endpoint returns either jobId or query results including with pagination, sinceDate, generated SQL query, current page record count and total records count.
Request
Query Parameters
-
configCode(required): string
The configuration name.Allowed Values:
[ "ds_p6adminuser", "ds_p6reportuser", "ds_unifier" ]
Supported Media Types
- application/json
Root Schema : QueryObject
Type:
object
Title:
QueryObject
The input object to fetch table data.
Show Source
-
mode: string
Allowed Values:
[ "SYNC", "ASYNC", "BLOB_COLUMNS" ]
A type of execution -
name: string
Query identifier.
-
nextKey: string
First row number and this will required only for pagination, fetch this value from previous pagination query response. A value "-1" indicates no more data on server.
-
nextTableName: string
Table Name.This will required only for pagination with multiple tables,fetch this value from previous pagination query response. A value "-1" indicates no more data on server.
-
pageSize: string
Specifies how many rows should return from this query.
-
sinceDate: string
A database date that the query fetches rows created/updated after this date.
-
sqlQueriesAndTotalRecordCount: boolean
Generated sql queries and total count from request body. By default this will be disabled(false).
-
tables(required): array
tables
A list of queries for tables.
Nested Schema : tables
Type:
array
A list of queries for tables.
Show Source
-
Array of:
object TableQuery
Title:
TableQuery
A list of queries for tables.
Example:
"tables": [ { "tableName": "Activity", "columns": [ "ACTIVITY_ID", "Name", "ACTIVITY_TYPE", "STATUS", "START_DATE", "Finish" ], "condition": { "operator": "AND", "conditions": [ { "columnName": "Planned Duration", "operator": "GREATER_THAN_OR_EQUALS", "value1": "1200" }, { "columnName": "PROJ_ID", "operator": "EQUALS", "value1": "1" } ] } } ]
Nested Schema : TableQuery
Type:
object
Title:
TableQuery
A list of queries for tables.
Show Source
-
columns(required): array
columns
A comma separated list of columns.
-
condition: object
Condition
Title:
Condition
The criteria of the query -
joinCondition: object
Condition
Title:
Condition
The criteria of the query -
joinedTables: array
joinedTables
Joined tables.
-
orderByColumns: array
orderByColumns
The order by columns on views/synonyms.
-
tableName(required): string
The table name.
Example:
"tables": [ { "tableName": "Activity", "columns": [ "ACTIVITY_ID", "Name", "ACTIVITY_TYPE", "STATUS", "START_DATE", "Finish" ], "condition": { "operator": "AND", "conditions": [ { "columnName": "Planned Duration", "operator": "GREATER_THAN_OR_EQUALS", "value1": "1200" }, { "columnName": "PROJ_ID", "operator": "EQUALS", "value1": "1" } ] } } ]
Nested Schema : Condition
Type:
object
Title:
Condition
The criteria of the query
Show Source
-
columnName: string
The column name. This property is ignored when the operator is AND/OR.
-
columnName1: string
The column name. This property is ignored when the operator is AND/OR.
-
columnName2: string
The column name from joined table. right column. This property is ignored for normal condition
-
conditions: array
conditions
List of Conditions if the operator is AND/OR.
-
joinConditions: array
joinConditions
List of Join Conditions with operator AND for an inner table
-
operator: string
Allowed Values:
[ "AND", "OR", "IS_NULL", "IS_NOT_NULL", "EQUALS", "NOT_EQUALS", "GREATER_THAN", "LESS_THAN", "GREATER_THAN_OR_EQUALS", "LESS_THAN_OR_EQUALS", "BETWEEN" ]
The condition operator. -
sqlFunction: string
-
value1: string
The first value of the criteria. When the operator is AND/OR/IS_NULL/IS_NOT_NULL, this property is ignored.
-
value2: string
The second value of the criteria. This property is used only when the operator is BETWEEN.
Nested Schema : joinedTables
Type:
array
Joined tables.
Show Source
-
Array of:
object TableQuery
Title:
TableQuery
A list of queries for tables.
Nested Schema : conditions
Type:
array
List of Conditions if the operator is AND/OR.
Show Source
-
Array of:
object Condition
Title:
Condition
The criteria of the query
Nested Schema : joinConditions
Type:
array
List of Join Conditions with operator AND for an inner table
Show Source
-
Array of:
object Condition
Title:
Condition
The criteria of the query
Response
Supported Media Types
- application/json
200 Response
Successful Operation
Root Schema : DataObject
Type:
object
Title:
DataObject
The response entity.
Show Source
-
data: object
data
The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.
-
jobId: string
(uuid)
The unique identifier for the background service. This will exists in response only if the mode is either ASYNC or BLOB_COLUMNS.
Nested Schema : data
Type:
object
The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.
202 Response
Accepted
400 Response
Bad Request
500 Response
Internal Server Error
503 Response
Service Unavailable