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

Query Parameters

• configCode(required): string
  The configuration name.

  Allowed Values: [ "ds_p6adminuser", "ds_p6reportuser", "ds_unifier" ]

Supported Media Types

• application/json

Request Body - 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.

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

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.

{
    "type":"array",
    "description":"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\"          }        ]      }    }  ]",
    "exampleSetFlag":true,
    "items":{
        "$ref":"#/components/schemas/TableQuery",
        "exampleSetFlag":false
    },
    "types":[
        "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"          }        ]      }    }  ]

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.

{
    "title":"TableQuery",
    "required":[
        "columns",
        "tableName"
    ],
    "type":"object",
    "properties":{
        "tableName":{
            "type":"string",
            "description":"The table name.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "columns":{
            "type":"array",
            "description":"A comma separated list of columns.",
            "exampleSetFlag":false,
            "items":{
                "type":"string",
                "exampleSetFlag":false,
                "types":[
                    "string"
                ]
            },
            "types":[
                "array"
            ]
        },
        "condition":{
            "$ref":"#/components/schemas/Condition",
            "exampleSetFlag":false
        },
        "joinedTables":{
            "type":"array",
            "description":"Joined tables.",
            "exampleSetFlag":false,
            "items":{
                "$ref":"#/components/schemas/TableQuery",
                "exampleSetFlag":false
            },
            "types":[
                "array"
            ]
        },
        "joinCondition":{
            "$ref":"#/components/schemas/Condition",
            "exampleSetFlag":false
        },
        "orderByColumns":{
            "type":"array",
            "description":"The order by columns on views/synonyms.",
            "exampleSetFlag":false,
            "items":{
                "type":"string",
                "exampleSetFlag":false,
                "types":[
                    "string"
                ]
            },
            "types":[
                "array"
            ]
        }
    },
    "description":"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\"          }        ]      }    }  ]",
    "exampleSetFlag":true
}

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 : columns

Type: array

A comma separated list of columns.

Show Source

• Array of: string

{
    "type":"array",
    "description":"A comma separated list of columns.",
    "exampleSetFlag":false,
    "items":{
        "type":"string",
        "exampleSetFlag":false,
        "types":[
            "string"
        ]
    },
    "types":[
        "array"
    ]
}

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.

{
    "title":"Condition",
    "type":"object",
    "properties":{
        "columnName":{
            "type":"string",
            "description":"The column name. This property is ignored when the operator is AND/OR.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "operator":{
            "type":"string",
            "description":"The condition operator.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ],
            "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.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "value2":{
            "type":"string",
            "description":"The second value of the criteria. This property is used only when the operator is BETWEEN.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "conditions":{
            "type":"array",
            "description":"List of Conditions if the operator is AND/OR.",
            "exampleSetFlag":false,
            "items":{
                "$ref":"#/components/schemas/Condition",
                "exampleSetFlag":false
            },
            "types":[
                "array"
            ]
        },
        "joinConditions":{
            "type":"array",
            "description":"List of Join Conditions with operator AND for an inner table",
            "exampleSetFlag":false,
            "items":{
                "$ref":"#/components/schemas/Condition",
                "exampleSetFlag":false
            },
            "types":[
                "array"
            ]
        },
        "sqlFunction":{
            "type":"string",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "columnName1":{
            "type":"string",
            "description":"The column name. This property is ignored when the operator is AND/OR.",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        },
        "columnName2":{
            "type":"string",
            "description":"The column name from joined table. right column. This property is ignored for normal condition",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        }
    },
    "description":"The criteria of the query",
    "exampleSetFlag":false
}

Nested Schema : joinedTables

Type: array

Joined tables.

Show Source

• Array of: object TableQuery
  Title: TableQuery

  A list of queries for tables.

{
    "type":"array",
    "description":"Joined tables.",
    "exampleSetFlag":false,
    "items":{
        "$ref":"#/components/schemas/TableQuery",
        "exampleSetFlag":false
    },
    "types":[
        "array"
    ]
}

Nested Schema : orderByColumns

Type: array

The order by columns on views/synonyms.

Show Source

• Array of: string

{
    "type":"array",
    "description":"The order by columns on views/synonyms.",
    "exampleSetFlag":false,
    "items":{
        "type":"string",
        "exampleSetFlag":false,
        "types":[
            "string"
        ]
    },
    "types":[
        "array"
    ]
}

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

{
    "type":"array",
    "description":"List of Conditions if the operator is AND/OR.",
    "exampleSetFlag":false,
    "items":{
        "$ref":"#/components/schemas/Condition",
        "exampleSetFlag":false
    },
    "types":[
        "array"
    ]
}

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

{
    "type":"array",
    "description":"List of Join Conditions with operator AND for an inner table",
    "exampleSetFlag":false,
    "items":{
        "$ref":"#/components/schemas/Condition",
        "exampleSetFlag":false
    },
    "types":[
        "array"
    ]
}

Back to Top

Response

Supported Media Types

• application/json

200 Response

Successful Operation

Body ()

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.

{
    "title":"DataObject",
    "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.",
            "exampleSetFlag":false,
            "types":[
                "object"
            ],
            "jsonSchema":{
                "type":"object",
                "description":"The query response which contains pagination and sinceDate details.This will exists in response only if the mode is SYNC."
            }
        },
        "jobId":{
            "type":"string",
            "description":"The unique identifier for the background service. This will exists in response only if the mode is either ASYNC or BLOB_COLUMNS.",
            "format":"uuid",
            "exampleSetFlag":false,
            "types":[
                "string"
            ]
        }
    },
    "description":"The response entity.",
    "exampleSetFlag":false
}

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

Back to Top