Read the data

Common API is the core API provided by PDM, it brings rich functionalities to meet a variety of requirements of data persistence and queries

User can leverage and combine the multiple query criteria to filter out any kind of data as needed, the main query conditions including: specified select list, criteria filters, pagination, sorting, aggregation, having filters, simple join, dynamic join etc.

The following sections will lead you fully understand the API step by step.

1. Request URL

{PDM Server domain}/common

HTTP Method： POST

2. Request headers

Name Value

Content-Type

application/json

3. Request body

Name Type Required Default value Example Description

boName

string

yes

``WELL''

boName is the model name pre-defined in metadata table BUSINESS_OBJECT and case insensitive

language

string

yes

``en''

language is required and it is case sensitive, only en value is supported for now

timezone

string

yes

``GMT+05:00''

timezone is required and it is case sensitive. Timezone value should follow the standard of GMT, e.g. GMT+xx:xx, GMT-xx:xx

selectList

string array

no

*

optional and default value is *, which means all the attributes will be read out.

showSQLStats

bool

no

false

true

A flag indicate if you need a basic quota of query efficiency, If it is true, a standard SQL, executionCount, totalTimeInMillis will be returned.

criteriaFilters

object array

no

Where conditions

pagination

object

no

Two parameters involved:

orderBy

object array

no

Sorting

aggregateSelectList

object array

no

Aggregate conditions

havingFilters

object array

no

readUnique

bool

no

false

``true''

If it is true but the database has more than one record then the API will response with error message

readFirst

bool

no

false

``true''

If set to true then exactly one record will be read. Can have zero records found

readMetadata

bool

no

false

``false''

The model’s metadata(``BUSINESS OBJECT ATTR'') will be returned If it is true, *metadata is the definition of the model

readMetadataConstraints

bool

no

false

``false''

The model’s unique constraints(``BUS OBJ ATTR UNIQ CONSTRAINTS'') will be returned if it is true

readReferenceData

bool

no

false

``false''

_if set to true need set readMetadata to true, and response will include the referencedata(``REFERENCE_DATA'') for the given BO_NAME only. _

readRecordsCount

bool

no

false

``false''

If set to true then response will include the total records count for the given BO Name and filters

readWithDistinct

bool

no

false

``false''

If set to true then distinct will be applied to the SQL SELECT LIST and also to the count if selected

readAllRecords

bool

no

false

``false''

If set to true then you will be able to read 10000 records at max in one query.

joinAlias

string

no

``a''

simpleJoins

object array

no

dynamicJoins

object array

no

prependAlias

bool

no

false

false

It will have effect only with the simple joins. If it is set to true then join alias will be prefixed to all attributes with one underscore in between. It is useful to identify an attribute when more than one attribute from different business objects have same name.

showSQLStats

bool

no

false

false

If set to true then the printed json response will include the list of sql statements executed for this request along with the count and time taken

collectSQLScript

integer

no

0

1

If this attribute is provided in the request and its value is between 1 and 11 inclusive then the response will include all the* sql scripts executed by the system. Possible values are from zero to eleven. No means no sql script to collect and 11 mains all sql script to collect in response. Following are the possible values* // 0: No SQL Script At All (Default) // 1: COUNT Only // 2: SELECT ONLY // 3: SELECT and COUNT // 4: INSERT Only // 5: UPDATE Only // 6: DELETE Only // 7: INSERT and UPDATE Only // 8: ALL DML Operations Only // 9: ALL DDL Operations Only // 10: ALL DDL and DML Operations Only

  • All keys are case sensitive

  • Values for keys boName and boAttrName are case insensitive.

  • Some keys have a predefined format for their values like language, timezone, date time, etc.
All acceptable/possible time formats

  • All acceptable/possible date only and timestamp and time only formats which can be parsed by the API.

  • Following is the list of all possible supported parse-able patterns. We start parsing from a more detail pattern and if not matched then we try to match a less detailed pattern till end.

  • If no match is found then API will throw an error.

 
TIME_ONLY_FORMAT("HH:mm:ss")
TIME_ONLY_FORMAT_WITH_MILLIS("HH:mm:ss.SSS")
// HYPHEN BASED DATE FORMATS
DATE_ONLY_FORMAT("yyyy-MM-dd")
DATETIME_FORMAT_WITHOUT_SECONDS("yyyy-MM-dd'T'HH:mm")
DATETIME_FORMAT_WITH_T("yyyy-MM-dd'T'HH:mm:ss")
DATETIME_FORMAT("yyyy-MM-dd HH:mm:ss")
DATETIME_FORMAT_WITH_TIMEZONE("yyyy-MM-dd HH:mm:ssX")
TIMESTAMP_FORMAT_WITHOUT_TIMEZONE("yyyy-MM-dd HH:mm:ss.SSS")
TIMESTAMP_FORMAT_WITH_TIMEZONE("yyyy-MM-dd HH:mm:ss.SSSX")
TIMESTAMP_ISO_FORMAT_UTC("yyyy-MM-dd'T'HH:mm:ss'Z'")
TIMESTAMP_ISO_FORMAT_WITH_MILLIS_UTC("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
TIMESTAMP_ISO_FORMAT_WITH_TIMEZONE("yyyy-MM-dd'T'HH:mm:ssX")
TIMESTAMP_ISO_FORMAT_WITH_TIMEZONE_AND_MILLIS("yyyy-MM-dd'T'HH:mm:ss.SSSX")
// SLASH BASED DATE FORMATS
DATE_ONLY_SLASH_FORMAT("yyyy/MM/dd")
DATETIME_SLASH_FORMAT_WITHOUT_SECONDS("yyyy/MM/dd'T'HH:mm")
DATETIME_SLASH_FORMAT_WITH_T("yyyy-MM-dd'T'HH:mm:ss")
DATETIME_SLASH_FORMAT("yyyy/MM/dd HH:mm:ss")
DATETIME_SLASH_FORMAT_WITH_TIMEZONE("yyyy/MM/dd HH:mm:ssX")
TIMESTAMP_SLASH_FORMAT_WITHOUT_TIMEZONE("yyy/MM/dd HH:mm:ss.SSS")
TIMESTAMP_SLASH_FORMAT_WITH_TIMEZONE("yyy/MM/dd HH:mm:ss.SSSX")
TIMESTAMP_ISO_SLASH_FORMAT_UTC("yyy/MM/dd'T'HH:mm:ss'Z'")
TIMESTAMP_ISO_SLASH_FORMAT_WITH_MILLIS_UTC("yyy/MM/dd'T'HH:mm:ss.SSS'Z'")
TIMESTAMP_ISO_SLASH_FORMAT_WITH_TIMEZONE("yyy/MM/dd'T'HH:mm:ssX")
TIMESTAMP_ISO_SLASH_FORMAT_WITH_TIMEZONE_AND_MILLIS("yyy/MM/dd'T'HH:mm:ss.SSSX")

3.1. Required and basic

Query the specified table.

Example 1. Query the specified table.

Query the well data. (The default is to query the first page)

{
    "boName": "WELL",
    "language": "en",
    "timezone": "GMT+05:00"
}

The final executed SQL:

SELECT * FROM WELL
FETCH NEXT (20) ROWS ONLY

+

Try it myself

Parameters

Name Type Required Default value Example value Description

boName

string

yes

``WELL''

boName is pre-defined in metadata table BUSINESS_OBJECT and case insensitive. Get all boName

language

string

yes

``en''

language is required and it is case sensitive and only en value is supported for now

timezone

string

yes

``GMT+05:00''

timezone is required and it is case sensitive. Timezone value only supports specific format GMT+xx:xx, GMT-xx:xx

Get all boName

You can copy the following json as body to get all boName. Specific parameters will be explained later.

{
   "boName":"BUSINESS OBJECT",
   "language":"en",
   "timezone":"GMT+08:00",
   "selectList":[
      "BO_NAME"
   ],
   "orderBy":[
      {
         "boAttrName":"business_object_id",
         "order":"ASC"
      }
   ],
   "readAllRecords":true
}

Get boAttrName by boName

You can copy the following json(replace values to specific boName) as body to get boAttrName by boName. Specific parameters will be explained later.

{
   "boName":"BUSINESS OBJECT ATTR",
   "language":"en",
   "timezone":"GMT+08:00",
   "selectList":[
      "BO_ATTR_NAME"
   ],
   "criteriaFilters":[
      {
         "boAttrName":"BO_NAME",
         "operator":"EQUALS",
         "values":[
            "WELL"
         ]
      }
   ],
   "orderBy":[
      {
         "boAttrName":"business_object_attr_id",
         "order":"ASC"
      }
   ],
   "readAllRecords":true
}

3.2. selectList

Return data from a column list.

Example 2. Query the well data. (The default is to query the first page) Only query columns: WELL_NAME'',AREA_NAME'', and return the SQL statement.
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "selectList":[
      "WELL_NAME",
      "AREA_NAME"
   ],
   "showSQLStats":true
}

The final executed SQL:

SELECT WELL_NAME, AREA_NAME FROM WELL
FETCH NEXT (20) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

selectList

string array

no

*

optional and default is * means all the columns defined in metadata will be selected.

showSQLStats

bool

no

false

true

If true, query sql, executionCount, totalTimeInMillis will be returned.

3.3. criteriaFilters

Where condition

Example 3. Query the well data. (The default is to query the first page) Query WELL_TYPE=OIL'' and WELL_NAME like CN'' and AREA_ID in(1,2,3,4,5) and WELL_ID between(1,10000)
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "criteriaFilters":[
      {
         "boAttrName":"WELL_TYPE",
         "operator":"EQUALS",
         "values":[
            "OIL"
         ]
      },
      {
         "boAttrName":"WELL_NAME",
         "operator":"LIKE",
         "values":[
            "CN"
         ]
      },
      {
         "boAttrName":"AREA_ID",
         "operator":"IN",
         "values":[
            1,
            2,
            3,
            4,
            5
         ]
      },
      {
         "boAttrName":"WELL_ID",
         "operator":"BETWEEN",
         "values":[
            1,
            10000
         ]
      }
   ]
}

The final executed SQL:

SELECT * FROM WELL
WHERE (WELL_TYPE = 'OIL')
    AND (WELL_NAME LIKE '%CN%')
    AND (AREA_ID IN (1, 2, 3, 4, 5))
    AND (WELL_ID BETWEEN 1 AND 10000)
FETCH NEXT (20) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

criteriaFilters

object array

no

Where conditions

criteriaFilters.Parameters

Name Type Required Default value Example value Description

boAttrName

string

yes

``UWI''

boAttrName is pre-defined in metadata table BUSINESS_OBJECT_ATTR and case insensitive. Get boAttrName by boName

operator

string

yes

LIKE

SQL operator, SUPPORTED OPERATORS please see criteriaFilters.Operators

values

array

yes

[``UWI-12345'']

key values is an array of values

criteriaFilters.Operators

All Operators are case sensitive

Name Description

EQUALS

=

NOT_EQUALS

!=

GREATER_THAN

>

LESS_THAN

<

GREATER_OR_EQUALS

>=

LESS_OR_EQUALS

⇐

BETWEEN

between

NOT_BETWEEN

not between

IN

in

NOT_IN

not in

LIKE

like

NOT_LIKE

not like
Note

Following operators are Postgres Only. VDB connection will not support these and will throw error incase we use them over VDB or on some other databases. JSONB operators are being used in full text search only for now.

Name Description

ILIKE

ilike

NOT_ILIKE

not ilike

JSONB_FIND_EXACT

jsonb find exact

JSONB_FIND_LIKE

jsonb find like

JSONB_DOT

jsonb dot

JSONB_DOT_FOR_TEXT

jsonb dot for text

3.4. pagination

Query the data for the specified page

Example 4. Query the well data on the second page and per page displays 30.
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "pagination":{
      "recordsPerPage":30,
      "pages":[
         2
      ]
   }
}

The final executed SQL:

SELECT * FROM WELL
OFFSET (30) ROWS
FETCH NEXT (30) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

pagination

object

no

3.5. orderBy

Order condition

Example 5. Query the well data and sort by UWI'' ascending and WELL_NAME'' Descending.
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "orderBy": [
    {
      "boAttrName": "UWI",
      "order": "ASC"
    },
    {
      "boAttrName": "WELL_NAME",
      "order": "DESC"
    }
  ]
}

The final executed SQL:

SELECT * FROM WELL
ORDER BY UWI ASC, WELL_NAME DESC
FETCH NEXT (20) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

orderBy

object array

no

3.6. aggregateSelectList

Aggregate conditions

Example 6. Aggregate Max WELL_ID and Min WELL_ID
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "aggregateSelectList":[
      {
         "boAttrName":"WELL_ID",
         "aggregateFunction":"MAX",
         "alias":"MAX_WELL_ID"
      },
      {
         "boAttrName":"WELL_ID",
         "aggregateFunction":"MIN",
         "alias":"MIN_WELL_ID"
      }
   ]
}

The final executed SQL of Example:

SELECT MAX(WELL_ID) as MAX_WELL_ID, MIN(WELL_ID) as MIN_WELL_ID FROM WELL
FETCH NEXT (20) ROWS ONLY
Example 7. Query WELL_NAME,AREA_NAME and Aggregate Max WELL_ID and Min WELL_ID
{
   "boName":"WELL",
   "language":"en",
   "timezone":"GMT+05:00",
   "selectList":[
      "WELL_NAME",
      "AREA_NAME"
   ],
   "aggregateSelectList":[
      {
         "boAttrName":"WELL_ID",
         "aggregateFunction":"MAX",
         "alias":"MAX_WELL_ID"
      },
      {
         "boAttrName":"WELL_ID",
         "aggregateFunction":"MIN",
         "alias":"MIN_WELL_ID"
      }
   ]
}

The final executed SQL of Example:

SELECT WELL_NAME, AREA_NAME, MAX(WELL_ID) as MAX_WELL_ID,
MIN(WELL_ID) as MIN_WELL_ID FROM WELL
GROUP BY WELL_NAME, AREA_NAME
FETCH NEXT (20) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

aggregateSelectList

object array

no

Aggregate conditions

aggregateSelectList.Parameters

Name Type Required Default value Example value Description

boAttrName

string

yes

``WELL_ID''

boAttrName is pre-defined in metadata table BUSINESS_OBJECT_ATTR and case insensitive. Get boAttrName by boName

aggregateFunction

string

yes

``MAX''

support MIN, MAX, SUM, AVG and Count

alias

string

yes

``MAX_WELL_ID''

will be used to get back the value from the response

3.7. havingFilters

Having condition

Example 8. Read Max of VOLUME_DATE For All Wells Which Were Created Before 2019 And Their BOE_VOLUME Is Not NULL
{
   "boName":"WELL VOL DAILY",
   "language":"en",
   "timezone":"GMT+05:00",
   "selectList":[
      "UWI"
   ],
   "aggregateSelectList":[
      {
         "boAttrName":"VOLUME_DATE",
         "aggregateFunction":"MAX",
         "alias":"MAX_VOLUME_DATE"
      }
   ],
   "criteriaFilters":[
      {
         "boAttrName":"BOE_VOLUME",
         "operator":"NOT_EQUALS",
         "values":[
            null
         ]
      }
   ],
   "havingFilters":[
      {
         "aggregateColumn":{
            "boAttrName":"ROW_CREATED_DATE",
            "aggregateFunction":"MAX"
         },
         "operator":"LESS_THAN",
         "values":[
            "2019-01-01 00:00:00+05:00"
         ]
      }
   ]
}

The final executed SQL of Example1:

SELECT UWI, MAX(VOLUME_DATE) as MAX_VOLUME_DATE FROM WELL_VOL_DAILY
WHERE (BOE_VOLUME IS NOT NULL)
GROUP BY UWI
HAVING (MAX(ROW_CREATED_DATE) < '2019-01-01 00:00:00+05:00')
FETCH NEXT (20) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

havingFilters

object array

no

havingFilters.Parameters

Name Type Required Default value Example value Description

aggregateColumn

object

yes

operator

string

yes

``LESS_THAN''

supported please see havingFilters.Operators

values

object array

yes

key values is an array of values

havingFilters.Operators

All Operators are case sensitive

Name Description

EQUALS

=

NOT_EQUALS

!=

GREATER_THAN

>

LESS_THAN

<

GREATER_OR_EQUALS

>=

LESS_OR_EQUALS

⇐

BETWEEN

between

NOT_BETWEEN

not between

IN

in

NOT_IN

not in

LIKE

like

NOT_LIKE

not like

ILIKE

ilike

NOT_ILIKE

not ilike

3.8. read options

Various read options

Parameters

Name Type Required Default value Example value Description

readUnique

bool

no

false

``true''

If set to true and the database has more than one record then service will throw error

readFirst

bool

no

false

``true''

If set to true then exactly one record will be read. Can have zero records found

readMetadata

bool

no

false

``false''

If set to true then response will include the metadata(``BUSINESS OBJECT ATTR'') for the given BO_NAME only

readMetadataConstraints

bool

no

false

``false''

If set to true then response will include the metadata unique constraints(``BUS OBJ ATTR UNIQ CONSTRAINTS'') for the given BO_NAME only

readReferenceData

bool

no

false

``false''

_if set to true need set readMetadata to true, and response will include the referencedata(``REFERENCE_DATA'') for the given BO_NAME only. _

readRecordsCount

bool

no

false

``false''

If set to true then response will include the total records count for the given BO Name and filters

readWithDistinct

bool

no

false

``false''

If set to true then distinct will be applied to the SQL SELECT LIST and also to the count if selected

readAllRecords

bool

no

false

``false''

If set to true then you will be able to read 10000 records at max in one query.

3.9. simple join

Joins are a useful and powerful feature of relational databases. It allows us to read data from more than one table using a single SQL statement. When the join sql statement is executed each row of one table is mapped to all the rows of the other table. This mapping rows of one table to another table is call cross product. The resultant table of cross product can be of high volume which can be decreased by using join conditions and where clause filters. Simple join are those where the table structure of the joined table already exists.

Example 9. WELL and WELLBORE simple join.
{
  "boName": "WELL",
  "language": "en",
  "timezone": "GMT+05:00",
  "joinAlias": "a",
  "selectList": [
    "WELL_ID",
    "WELL_NAME",
    "AREA_NAME",
    "UWI"
  ],
  "simpleJoins": [
    {
      "boName": "WELLBORE",
      "joinType": "INNER",
      "joinAlias": "b",
      "selectList": [
        "WELLBORE_ID",
        "WELLBORE_NAME"
      ],
      "joiningConditions": [
        {
          "leftSide": {
            "joinAlias": "a",
            "boAttrName": "UWI"
          },
          "operator": "EQUALS",
          "rightSide": {
            "joinAlias": "b",
            "boAttrName": "WELL_UWI"
          }
        }
      ]
    }
  ],
  "prependAlias": false,
  "showSQLStats":true
}

The final executed SQL of Example:

SELECT a.WELL_ID, a.WELL_NAME, a.AREA_NAME, a.UWI,
b.WELLBORE_ID, b.WELLBORE_NAME
FROM WELL AS a  INNER JOIN WELLBORE AS b
ON a.UWI = b.WELL_UWI
FETCH NEXT (20) ROWS ONLY
Example 10. More joins and more combination conditions.
{
  "boName": "WELL",
  "language": "en",
  "timezone": "GMT+05:00",
  "joinAlias": "a",
  "selectList": [
    "WELL_ID",
    "WELL_NAME",
    "AREA_NAME",
    "UWI"
  ],
  "criteriaFilters": [
    {
      "boAttrName": "WELL_NAME",
      "operator": "LIKE",
      "values": [
        "P"
      ]
    }
  ],
  "pagination": {
    "recordsPerPage": 30,
    "pages": [
      1
    ]
  },
  "simpleJoins": [
    {
      "boName": "WELLBORE",
      "joinType": "INNER",
      "joinAlias": "b",
      "selectList": [
        "WELLBORE_ID",
        "WELLBORE_NAME"
      ],
      "joiningConditions": [
        {
          "leftSide": {
            "joinAlias": "a",
            "boAttrName": "UWI"
          },
          "operator": "EQUALS",
          "rightSide": {
            "joinAlias": "b",
            "boAttrName": "WELL_UWI"
          }
        }
      ],
      "criteriaFilters": [
        {
          "boAttrName": "WELL_UWI",
          "operator": "LIKE",
          "values": [
            "P"
          ]
        }
      ],
      "orderBy": [
        {
          "boAttrName": "WELL_UWI",
          "order": "DESC"
        }
      ]
    },
    {
      "boName": "well vol daily",
      "joinType": "INNER",
      "joinAlias": "c",
      "joiningConditions": [
        {
          "leftSide": {
            "joinAlias": "a",
            "boAttrName": "UWI"
          },
          "operator": "EQUALS",
          "rightSide": {
            "joinAlias": "c",
            "boAttrName": "UWI"
          }
        }
      ]
    }
  ],
  "prependAlias": false,
  "showSQLStats":true
}

The final executed SQL of Example:

SELECT a.WELL_ID, a.WELL_NAME, a.AREA_NAME, a.UWI,
b.WELLBORE_ID, b.WELLBORE_NAME
FROM WELL AS a
INNER JOIN WELLBORE AS b ON a.UWI = b.WELL_UWI
INNER JOIN WELL_VOL_DAILY AS c ON a.UWI = c.UWI
WHERE (CAST(a.WELL_NAME AS VARCHAR) LIKE '%P%')
    AND (CAST(b.WELL_UWI AS VARCHAR) LIKE '%P%')
ORDER BY b.WELL_UWI DESC
FETCH NEXT (30) ROWS ONLY
Try it myself

Parameters

Name Type Required Default value Example value Description

joinAlias

string

no

``a''

simpleJoins

object array

no

prependAlias

bool

no

false

false

It will have effect only with the simple or dynamic joins. If it is set to true then join alias will be prefixed to all attributes with one underscore in between. It is useful to identify an attribute when more than one attribute from different business objects have same name.

simpleJoins.Parameters

selectList, aggregateSelectList, criteriaFilters, havingFilters and orderBy can be used with simple join objects. Their effect will be on the whole result data.

But pagination cannot be used with simple joins objects.

Name Type Required Default value Example value Description

boName

string

yes

``WELL''

Bo Name is the name of business object to be joined. If it is same as the parent boName then it will be self-join. Get all boName

joinType

string

yes

``LEFT''

Join type is the type of join and it is case sensitive. Possible values are INNER, LEFT, RIGHT and FULL.

joinAlias

string

no

``b''

Join Alias is same as defined above but it will be defined here for the join table.

joiningConditions

object array

yes

3.10. dynamic join

Joins are a useful and powerful feature of relational databases. It allows us to read data from more than one table using a single SQL statement. When the join sql statement is executed each row of one table is mapped to all the rows of the other table. This mapping rows of one table to another table is call cross product. The resultant table of cross product can be of high volume which can be decreased by using join conditions and where clause filters. In dynamic joins the joined table is created by a nested SQL statement.

Example 11. Query table WELL_VOL_DAILY each UWI maximum date’s volume.
{
   "boName":"well vol daily",
   "language":"en",
   "timezone":"GMT+05:00",
   "joinAlias":"a",
   "readAllRecords":true,
   "selectList":[
      "UWI",
      "boe_volume"
   ],
   "dynamicJoins":[
      {
         "selectList":[
            "UWI",
            "max_volume_date"
         ],
         "joinType":"INNER",
         "joinAlias":"b",
         "dynamicTables":[
            {
               "boName":"well vol daily",
               "selectList":[
                  "UWI"
               ],
               "aggregateSelectList":[
                  {
                     "boAttrName":"volume_date",
                     "aggregateFunction":"MAX",
                     "alias":"max_volume_date"
                  }
               ]
            }
         ],
         "joiningConditions":[
            {
               "leftSide":{
                  "joinAlias":"a",
                  "boAttrName":"UWI"
               },
               "operator":"EQUALS",
               "rightSide":{
                  "joinAlias":"b",
                  "boAttrName":"UWI"
               }
            },
            {
               "leftSide":{
                  "joinAlias":"a",
                  "boAttrName":"volume_date"
               },
               "operator":"EQUALS",
               "rightSide":{
                  "joinAlias":"b",
                  "boAttrName":"max_volume_date"
               }
            }
         ],
         "orderBy":[
            {
               "boAttrName":"UWI",
               "order":"DESC"
            }
         ]
      }
   ],
   "showSQLStats":"true"
}

The final executed SQL of Example:

SELECT a.UWI, a.BOE_VOLUME,
b.UWI, b.max_volume_date
FROM WELL_VOL_DAILY AS a
INNER JOIN (SELECT UWI, MAX(VOLUME_DATE) AS "max_volume_date"
    FROM WELL_VOL_DAILY GROUP BY UWI) AS b
ON a.UWI = b.UWI AND a.VOLUME_DATE = b.max_volume_date
ORDER BY b.UWI desc
Try it myself

Parameters

Name Type Required Default value Example value Description

dynamicJoins

object array

no

dynamicJoins.Parameters

selectList, aggregateSelectList, criteriaFilters, havingFilters and orderBy can be used with dynamic join objects. Their effect will be on the whole result data.

But pagination cannot be used with dynamic joins objects.

Name Type Required Default value Example value Description

joinType

string

yes

``LEFT''

Join type is the type of join and it is case sensitive. Possible values are INNER, LEFT, RIGHT and FULL.

joinAlias

string

yes

``b''

Join Alias is same as defined above but it will be defined here for the join table.

dynamicTables

object array

yes

joiningConditions

object array

yes

dynamicTables.Parameters

selectList, aggregateSelectList, criteriaFilters, havingFilters and orderBy can be used with dynamicTables objects. Their effect will be on the whole result data.

But pagination cannot be used with dynamic joins objects.

Name Type Required Default value Example value Description

boName

string

yes

``WELL''

Bo Name is the name of business object to be read. Get all boName

joinType

string

no

``LEFT''

joinAlias

string

no

``b''

Join Alias is same as defined above but it will be defined here for the dynamic table.

joiningConditions

object array

no

4. Responses

Please see Responses