Jump to

• Request
• Response

Query the data for a list of tables.

post

/sync/rest-service/dataservice/runquery

The result contains the data from specified tables with specified columns. Invalid columns are ignored. There are two extra sections in the result: pagination information and timestamp for next incremental query.

Request

Supported Media Types

• application/json

Query Parameters

• configCode(optional): string
  The configuration name.

  Default Value: default

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

Body ()

The query in json format.

Root Schema : QueryObject

Type: object

The query object for restful service.

Show Source

• name(optional): string
  An optional query name.
• nextKey(optional): string
  Allowed Values: [ "The value comes from last query when there is more data on server" ]

  When there is more data on server, the result from last query returns nextKey property.
• nextTableName(optional): string
  Allowed Values: [ "The value comes from last query when there is more data on server" ]

  When there is more data on server, the result from last query returns nextTableName property. A value "-1" indicates no more data on server.
• pageSize(optional): string
  Allowed Values: [ "1 - 10000" ]

  Specifies how many rows returned from this query.
• sinceDate(optional): string
  Allowed Values: [ "The value should either be null or returned from last query" ]

  A database date that the query fetches rows created/updated after that date.
• tables: array tables
  A list of queries for tables.

{
    "type":"object",
    "required":[
        "tables"
    ],
    "properties":{
        "name":{
            "type":"string",
            "description":"An optional query name."
        },
        "sinceDate":{
            "type":"string",
            "description":"A database date that the query fetches rows created/updated after that date.",
            "enum":[
                "The value should either be null or returned from last query"
            ]
        },
        "pageSize":{
            "type":"string",
            "description":"Specifies how many rows returned from this query.",
            "enum":[
                "1 - 10000"
            ]
        },
        "nextTableName":{
            "type":"string",
            "description":"When there is more data on server, the result from last query returns nextTableName property. A value \"-1\" indicates no more data on server.",
            "enum":[
                "The value comes from last query when there is more data on server"
            ]
        },
        "nextKey":{
            "type":"string",
            "description":"When there is more data on server, the result from last query returns nextKey property.",
            "enum":[
                "The value comes from last query when there is more data on server"
            ]
        },
        "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 query object for restful service."
}

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 of the query.
• 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 from table name of the query.

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

Nested Schema : columns

Type: array

A comma separated list of columns of the query.

Show Source

• Array of: string

{
    "type":"array",
    "description":"A comma separated list of columns of the query.",
    "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 result data model

Show Source

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

  A list of tables, which contains rows of column data. It also returns a database date for the next query to retrieve fresh data only. If next query only cares about the data being created/updated after current query, it would supply sinceDate property from the current query in the next query. A pagination section is also returned in the response to indicate if there is more data on the server. When nextTableName="-1", no more data available.The next query should use original query PLUS pagination information to retrieve the remaining data on the server.

{
    "type":"object",
    "properties":{
        "data":{
            "type":"object",
            "description":"A list of tables, which contains rows of column data. It also returns a database date for the next query to retrieve fresh data only. If next query only cares about the data being created/updated after current query, it would supply sinceDate property from the current query in the next query. A pagination section is also returned in the response to indicate if there is more data on the server. When nextTableName=\"-1\", no more data available.The next query should use original query PLUS pagination information to retrieve the remaining data on the server.",
            "additionalProperties":{
                "type":"array",
                "items":{
                    "type":"object",
                    "additionalProperties":{
                        "type":"string"
                    }
                }
            }
        }
    },
    "description":"the result data model"
}

Nested Schema : data

Type: object

Additional Properties Allowed

Show Source

• array additionalProperties

{
    "type":"object",
    "description":"A list of tables, which contains rows of column data. It also returns a database date for the next query to retrieve fresh data only. If next query only cares about the data being created/updated after current query, it would supply sinceDate property from the current query in the next query. A pagination section is also returned in the response to indicate if there is more data on the server. When nextTableName=\"-1\", no more data available.The next query should use original query PLUS pagination information to retrieve the remaining data on the server.",
    "additionalProperties":{
        "type":"array",
        "items":{
            "type":"object",
            "additionalProperties":{
                "type":"string"
            }
        }
    }
}

A list of tables, which contains rows of column data. It also returns a database date for the next query to retrieve fresh data only. If next query only cares about the data being created/updated after current query, it would supply sinceDate property from the current query in the next query. A pagination section is also returned in the response to indicate if there is more data on the server. When nextTableName="-1", no more data available.The next query should use original query PLUS pagination information to retrieve the remaining data on the server.

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"
    }
}

400 Response

Bad Request

500 Response

Internal Server Error

Back to Top