Jump to

• Request
• Response

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

Supported Media Types

• application/json

Query Parameters

• configCode: string
  The configuration name.

Body ()

The query details in json format.

Root Schema : QueryObject

Type: object

The input object to fetch table data.

Show Source

• mode(optional): string
  Allowed Values: [ "SYNC", "ASYNC", "BLOB_COLUMNS" ]

  A type of execution
• name(optional): string
  Query identifier.
• nextKey(optional): 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(optional): 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(optional): string
  Specifies how many rows should return from this query.
• sinceDate(optional): string
  A database date that the query fetches rows created/updated after this date.
• sqlQueriesAndTotalRecordCount(optional): boolean
  Generated sql queries and total count from request body. By default this will be disabled(false).
• tables: array tables
  A list of queries for tables.

{
    "type":"object",
    "required":[
        "tables"
    ],
    "properties":{
        "name":{
            "type":"string",
            "description":"Query identifier."
        },
        "sinceDate":{
            "type":"string",
            "description":"A database date that the query fetches rows created/updated after this date."
        },
        "pageSize":{
            "type":"string",
            "description":"Specifies how many rows should return from this query."
        },
        "nextTableName":{
            "type":"string",
            "description":"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."
        },
        "nextKey":{
            "type":"string",
            "description":"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."
        },
        "sqlQueriesAndTotalRecordCount":{
            "type":"boolean",
            "description":"Generated sql queries and total count from request body. By default this will be disabled(false)."
        },
        "mode":{
            "type":"string",
            "description":"A type of execution",
            "enum":[
                "SYNC",
                "ASYNC",
                "BLOB_COLUMNS"
            ]
        },
        "tables":{
            "type":"array",
            "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\"          }        ]      }    }  ]",
            "description":"A list of queries for tables.",
            "items":{
                "$ref":"#/definitions/TableQuery"
            }
        }
    },
    "description":"The input object to fetch table data."
}

Nested Schema : tables

Type: array

A list of queries for tables.

Show Source

• Array of: object TableQuery

{
    "type":"array",
    "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\"          }        ]      }    }  ]",
    "description":"A list of queries for tables.",
    "items":{
        "$ref":"#/definitions/TableQuery"
    }
}

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

Show Source

• columns: array columns
  A comma separated list of columns.
• condition(optional): object The criteria of the query
• joinCondition(optional): object The criteria of the query
• joinedTables(optional): array joinedTables
  Joined tables.
• tableName: string
  The table name.

{
    "type":"object",
    "required":[
        "columns",
        "tableName"
    ],
    "properties":{
        "tableName":{
            "type":"string",
            "description":"The table name."
        },
        "columns":{
            "type":"array",
            "description":"A comma separated list of columns.",
            "items":{
                "type":"string"
            }
        },
        "condition":{
            "description":"The filter condition on table.",
            "$ref":"#/definitions/The criteria of the query"
        },
        "joinedTables":{
            "type":"array",
            "description":"Joined tables.",
            "items":{
                "$ref":"#/definitions/TableQuery"
            }
        },
        "joinCondition":{
            "description":"The filter condition on join table.",
            "$ref":"#/definitions/The criteria of the query"
        }
    }
}

Nested Schema : columns

Type: array

A comma separated list of columns.

Show Source

• Array of: string

{
    "type":"array",
    "description":"A comma separated list of columns.",
    "items":{
        "type":"string"
    }
}

Nested Schema : The criteria of the query

Type: object

Show Source

• columnName(optional): string
  The column name. This property is ignored when the operator is AND/OR.
• columnName1(optional): string
  The column name. This property is ignored when the operator is AND/OR.
• columnName2(optional): string
  The column name from joined table. right column. This property is ignored for normal condition
• conditions(optional): array conditions
  List of Conditions if the operator is AND/OR.
• joinConditions(optional): array joinConditions
  List of Join Conditions with operator AND for an inner table
• operator(optional): 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(optional): string
• value1(optional): string
  The first value of the criteria. When the operator is AND/OR/IS_NULL/IS_NOT_NULL, this property is ignored.
• value2(optional): string
  The second value of the criteria. This property is used only when the operator is BETWEEN.

{
    "type":"object",
    "properties":{
        "columnName":{
            "type":"string",
            "description":"The column name. This property is ignored when the operator is AND/OR."
        },
        "operator":{
            "type":"string",
            "description":"The condition operator.",
            "enum":[
                "AND",
                "OR",
                "IS_NULL",
                "IS_NOT_NULL",
                "EQUALS",
                "NOT_EQUALS",
                "GREATER_THAN",
                "LESS_THAN",
                "GREATER_THAN_OR_EQUALS",
                "LESS_THAN_OR_EQUALS",
                "BETWEEN"
            ]
        },
        "value1":{
            "type":"string",
            "description":"The first value of the criteria. When the operator is AND/OR/IS_NULL/IS_NOT_NULL, this property is ignored."
        },
        "value2":{
            "type":"string",
            "description":"The second value of the criteria. This property is used only when the operator is BETWEEN."
        },
        "conditions":{
            "type":"array",
            "description":"List of Conditions if the operator is AND/OR.",
            "items":{
                "$ref":"#/definitions/The criteria of the query"
            }
        },
        "joinConditions":{
            "type":"array",
            "description":"List of Join Conditions with operator AND for an inner table",
            "items":{
                "$ref":"#/definitions/The criteria of the query"
            }
        },
        "sqlFunction":{
            "type":"string"
        },
        "columnName1":{
            "type":"string",
            "description":"The column name. This property is ignored when the operator is AND/OR."
        },
        "columnName2":{
            "type":"string",
            "description":"The column name from joined table. right column. This property is ignored for normal condition"
        }
    }
}

Nested Schema : joinedTables

Type: array

Joined tables.

Show Source

• Array of: object TableQuery

{
    "type":"array",
    "description":"Joined tables.",
    "items":{
        "$ref":"#/definitions/TableQuery"
    }
}

Nested Schema : conditions

Type: array

List of Conditions if the operator is AND/OR.

Show Source

• Array of: object The criteria of the query

{
    "type":"array",
    "description":"List of Conditions if the operator is AND/OR.",
    "items":{
        "$ref":"#/definitions/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 The criteria of the query

{
    "type":"array",
    "description":"List of Join Conditions with operator AND for an inner table",
    "items":{
        "$ref":"#/definitions/The criteria of the query"
    }
}

Back to Top

Response

Supported Media Types

• application/json

200 Response

successful operation

Body ()

Root Schema : DataObject

Type: object

The response entity.

Show Source

• data(optional): object data
  Additional Properties Allowed: additionalProperties

  The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.
• jobId(optional): string(uuid)
  The unique identifier for the background service. This will exists in response only if the mode is either ASYNC or BLOB_COLUMNS.

{
    "type":"object",
    "properties":{
        "data":{
            "type":"object",
            "description":"The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.",
            "additionalProperties":{
                "type":"array",
                "items":{
                    "type":"object",
                    "additionalProperties":{
                        "type":"string"
                    }
                }
            }
        },
        "jobId":{
            "type":"string",
            "format":"uuid",
            "description":"The unique identifier for the background service. This will exists in response only if the mode is either ASYNC or BLOB_COLUMNS."
        }
    },
    "description":"The response entity."
}

Nested Schema : data

Type: object

Additional Properties Allowed

Show Source

• array additionalProperties

{
    "type":"object",
    "description":"The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.",
    "additionalProperties":{
        "type":"array",
        "items":{
            "type":"object",
            "additionalProperties":{
                "type":"string"
            }
        }
    }
}

The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC.

Nested Schema : additionalProperties

Type: array

Show Source

• Array of: object items
  Additional Properties Allowed: additionalProperties

{
    "type":"array",
    "items":{
        "type":"object",
        "additionalProperties":{
            "type":"string"
        }
    }
}

Nested Schema : items

Type: object

Additional Properties Allowed

Show Source

• string

{
    "type":"object",
    "additionalProperties":{
        "type":"string"
    }
}

202 Response

Request Accepted

400 Response

Bad Request

500 Response

Internal Server Error

503 Response

Service Unavailable

Back to Top