Additional Data Service Query Examples
Data service query using BETWEEN operator for filtering date values
{
"name": "INVOICE",
"pageSize": "1000",
"tables": [
{
"tableName": "W_INVOICE_D",
"columns": [
"DATASOURCE_ID",
"INVOICE_OBJECT_ID",
"DATE_APPROVED",
"DATE_AUTHORIZED",
"PAYMENT_STATUS",
"INVOICE_NUMBER"
],
"condition": {
"operator": "AND",
"conditions": [
{
"columnName": "DATE_APPROVED",
"operator": "BETWEEN",
"value1": "20-OCT-2018",
"value2": "20-DEC-2018"
}
]
}
}
]
}
Data service query using simple Explicit Join when there is no referential integrity between tables
{
"name": "BUDGET",
"pageSize": "1000",
"tables": [
{
"tableName": "W_BUDGET_F",
"columns": [
"DATASOURCE_ID",
"BUDGET_OBJECT_ID",
"PROJECT_OBJECT_ID",
"GC_OBJECT_ID",
"SUBCONTRACTOR_OBJECT_ID",
"CONTRACT_OBJECT_ID",
"BUDGET_AMOUNT",
"BALANCE"
],
"joinedTables": [
{
"tableName": "W_PAYMENT_D",
"columns": [
"PAYMENT_OBJECT_ID",
"PAYMENTCODE"
],
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_PAYMENT_D.PROJECT_OBJECT_ID"
}
}
]
}
]
}
Data service query joining multiple inner tables
{
"name": "BUDGET",
"pageSize": "1000",
"tables": [
{
"tableName": "W_BUDGET_F",
"columns": [
"DATASOURCE_ID",
"BUDGET_OBJECT_ID",
"PROJECT_OBJECT_ID",
"GC_OBJECT_ID",
"SUBCONTRACTOR_OBJECT_ID",
"CONTRACT_OBJECT_ID",
"BUDGET_AMOUNT",
"BALANCE"
],
"joinedTables": [
{
"tableName": "W_PAYMENT_D",
"columns": [
"PAYMENT_OBJECT_ID",
"PAYMENTCODE"
],
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_PAYMENT_D.PROJECT_OBJECT_ID"
}
},
{
"tableName": "W_CONTRACT_D",
"columns": [
"CONTRACT_OBJECT_ID",
"CONTRACT_NUMBER"
],
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_CONTRACT_D.PROJECT_OBJECT_ID"
}
}
]
}
]
}
Data service query joining single inner table with multiple conditions
{
"name": "BUDGET",
"pageSize": "1000",
"tables": [
{
"tableName": "W_BILLING_F",
"columns": [
"DATASOURCE_ID",
"BILLING_OBJECT_ID",
"PROJECT_OBJECT_ID",
"GC_OBJECT_ID",
"SUBCONTRACTOR_OBJECT_ID",
"CONTRACT_OBJECT_ID",
"TOTALWORKCOMPLETE",
"PENDINGPAYMENT"
],
"joinedTables": [
{
"tableName": "W_PAYMENT_F",
"columns": [
"PAYMENT_OBJECT_ID",
"INVOICE_OBJECT_ID",
"PAYMENTAMOUNT"
],
"joinCondition": {
"operator": "AND",
"joinConditions": [
{
"columnName1": "W_BILLING_F.PROJECT_OBJECT_ID",
"columnName2": "W_PAYMENT_D.PROJECT_OBJECT_ID"
},
{
"columnName1": "W_BILLING_F.INVOICE_OBJECT_ID",
"columnName2": "W_PAYMENT_D.INVOICE_OBJECT_ID"
}
}
]
}
]
}
Data service query with Join and Data filter
{
"name": "BUDGET",
"pageSize": "1000",
"tables": [
{
"tableName": "W_BUDGET_F",
"columns": [
"DATASOURCE_ID",
"BUDGET_OBJECT_ID",
"PROJECT_OBJECT_ID",
"GC_OBJECT_ID",
"SUBCONTRACTOR_OBJECT_ID",
"CONTRACT_OBJECT_ID",
"BUDGET_AMOUNT",
"BALANCE"
],
"joinedTables": [
{
"tableName": "W_PAYMENT_D",
"columns": [
"PAYMENT_OBJECT_ID",
"PAYMENTCODE"
],
"condition": {
"operator": "AND",
"conditions": [
{
"columnName": "PAYMENTCODE",
"operator": "NOT_EQUALS",
"value1": "ACH",
"value2": null
}
]
},
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_PAYMENT_D.PROJECT_OBJECT_ID"
}
},
{
"tableName": "W_CONTRACT_D",
"columns": [
"CONTRACT_OBJECT_ID",
"CONTRACT_NUMBER"
],
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_CONTRACT_D.PROJECT_OBJECT_ID"
}
}
]
}
]
}
Data service complex query with Filters and Joins
{
"name": "BUDGET",
"pageSize": "1000",
"tables": [
{
"tableName": "W_BUDGET_F",
"columns": [
"DATASOURCE_ID",
"BUDGET_OBJECT_ID",
"PROJECT_OBJECT_ID",
"GC_OBJECT_ID",
"SUBCONTRACTOR_OBJECT_ID",
"CONTRACT_OBJECT_ID",
"BUDGET_AMOUNT",
"BALANCE"
],
"condition": {
"operator": "AND",
"conditions": [
{
"columnName": "BALANCE",
"operator": "GREATER_THAN",
"value1": "0",
"value2": null
}
]
},
"joinedTables": [
{
"tableName": "W_PAYMENT_D",
"columns": [
"PAYMENT_OBJECT_ID",
"PAYMENTCODE"
],
"condition": {
"operator": "AND",
"conditions": [
{
"columnName": "PAYMENTCODE",
"operator": "NOT_EQUALS",
"value1": "ACH",
"value2": null
}
]
},
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_PAYMENT_D.PROJECT_OBJECT_ID"
}
},
{
"tableName": "W_CONTRACT_D",
"columns": [
"CONTRACT_OBJECT_ID",
"CONTRACT_NUMBER"
],
"joinCondition": {
"columnName1": "W_BUDGET_F.PROJECT_OBJECT_ID",
"columnName2": "W_CONTRACT_D.PROJECT_OBJECT_ID"
}
}
]
}
]
}
Last Published Tuesday, February 6, 2024