Admin Query API




In a Nutshell

The Query API feature of the Admin module is used to setup and execute queries for Cloud-based data. Here, you can input and execute the CoreSQL query. The application will return a response to the query in JSON format.

Note The Admin Query API will, by default, use the latest version of the Data Transfer Object. If you would like to use a specific Data Transfer Object version, you can select the settings option, which will then enable you to declare a specific version of a supported data object.

A Closer Look

When you navigate to the Admin > Companies > Query API, you will see the following information:


Writing Queries

The following section describes in more detail how you can write your own CoreSQL queries. CoreSQL is limited to the SQL clauses and expressions described below.

Attention: Offset/Limit is not supported by the Admin Query API. It is recommended to use the Analytics & Reporting app to limit the number of records returned.

1 The SELECT Clause

By using this Admin Query API, we can query for resources in an SQL-like fashion, similar to querying in database tables.

The SELECT clause has similar semantics like in SQL. For example:

SELECT bp FROM BusinessPartner bp LIMIT 2

Here, we query for the first two resources of type BusinessPartner (bp). Each resource must have a declaration part. This is done in the FROM and/or JOIN clauses. In this case, the resource declaration is BusinessPartner bp in the FROM clause. The query above returns all the fields (defined in the BusinessPartner’s DTO version) of the first two Business Partners. If we want to get only some fields of the resource, a query like this can be performed:

SELECT bp.id, bp.lastChanged, bp.creditLimit FROM BusinessPartner bp

This query returns the fields id, lastChanged and creditLimit for all the Business Partners.

The Business Partner’s creditLimit is a complex field defined inside a Business Partner object. In the SELECT clause, we cannot ask for specific fields inside a complex type (ex. SELECT bp.creditLimit.amount FROM BusinessPartner bp).

On the other hand such fields can be used in expressions (ex. SELECT bp.creditLimit FROM BusinessPartner bp WHERE bp.creditLimit.amount>0)

Also, we can have more than one resource in a SELECT clause:

SELECT bp.id, bp.city, bp.name, sc.id, sc.subject FROM BusinessPartner bp JOIN ServiceCall sc ON bp=sc.businessPartner LIMIT 2

This query returns the first two pairs of (Business Partner, Service Call) that have this relation between them: bp=sc.businessPartner


1.1 Limitations

We cannot navigate between resources using the dot syntax. The example below will not work:

SELECT bp.paymentType.id FROM BusinessPartner bp

In this example, the paymentType is a different resource than the Business Partner (bp). If we want to get the paymentType.id, we have to make a query like:

SELECT pt.id FROM PaymentType pt JOIN BusinessPartner bp ON bp.paymentType=pt

We cannot ask for specific fields of a complex type. The example below will not work:

SELECT bp.creditLimit.amount FROM BusinessPartner bp

In this example, the creditLimit is a complex type inside the Business Partner. The SELECT clause requires that we get all the fields of a complex type or none of them. On the other hand, we can use fields of a complex type in WHERE, JOINs, ORDER BY expressions. This will work:

SELECT bp.creditLimit FROM BusinessPartner bp WHERE bp.creditLimit.amount>0

2 The FROM Clause

Each resource we use in the query must be declared in FROM or JOINs clauses. Like in SQL, a resource declaration looks like ResourceType resource

SELECT bp, sc FROM BusinessPartner bp, ServiceCall sc

In this example we have two declarations: BusinessPartner bp and ServiceCall sc, separated by a comma.

3 The WHERE Clause

Like in SQL, we use the WHERE clause for resource filtering based on expressions.

SELECT bp.name, 
       bp.lastChanged, 
       bp.creditLimit 
FROM   BusinessPartner bp 
WHERE  Upper(bp.name) LIKE '%TAY%' 
       AND bp.lastChanged > '2014-01-01'        

For more filtering possibilities, please visit the Expressions section.


4 The JOIN Clause

SELECT sc,
       bp
FROM   ServiceCall sc
       JOIN BusinessPartner bp
         ON bp = sc.businessPartner 
SELECT sc, 
       bp 
FROM   servicecall sc 
       INNER JOIN businesspartner bp 
               ON bp = sc.businesspartner 

Observation: Instead of using bp=sc.businessPartner we can use bp.id=sc.businessPartner (this is the same thing).


4.1 LEFT JOIN (or LEFT OUTER JOIN)

SELECT sc,
       bp
FROM   ServiceCall sc
       LEFT JOIN BusinessPartner bp
         ON bp = sc.businessPartner
SELECT sc,
       bp
FROM   ServiceCall sc
       LEFT OUTER JOIN BusinessPartner bp
                    ON bp = sc.businessPartner 

4.2 RIGHT JOIN (or RIGHT OUTER JOIN)

SELECT sc,
       bp
FROM   ServiceCall sc
       RIGHT JOIN BusinessPartner bp
         ON bp = sc.businessPartner
SELECT sc,
       bp
FROM   ServiceCall sc
       RIGHT OUTER JOIN BusinessPartner bp
                     ON bp = sc.businessPartner 

4.3 FULL OUTER JOIN

SELECT sc,
       bp
FROM   ServiceCall sc
       FULL OUTER JOIN BusinessPartner bp
                    ON bp = sc.businessPartner

According to the relation between resources, we support three join types:


4.4 Resources That Have a Direct Connection between Them

SELECT sc,
       bp
FROM   ServiceCall sc
       JOIN BusinessPartner bp
         ON bp = sc.businessPartner 
SELECT sc,
       bp
FROM   ServiceCall sc
       JOIN BusinessPartner bp
         ON bp.id = sc.businessPartner 

In this example, the ServiceCall (sc) resource has a field named businessPartner which is a reference to the resource BusinessPartner (bp).


4.5 Resources That Have an Indirect Connection between Them

All four queries below are equivalent:

SELECT a, 
       bp 
FROM   Activity a 
       JOIN BusinessPartner bp 
         ON a.object = bp 

SELECT a, 
       bp 
FROM   Activity a 
       JOIN BusinessPartner bp 
         ON a.object = bp.id 

SELECT a, 
       bp 
FROM   Activity a 
       JOIN BusinessPartner bp 
         ON a.object.objectid = bp 

SELECT a, 
       bp 
FROM   Activity a 
       JOIN BusinessPartner bp 
         ON a.object.objectid = bp.id 

In this case, an Activity has a field named object (this field, can refer to any resource). The joining condition says that, for this case, the resource is a BusinessPartner (bp).


4.6 Resources in Collections of Resources

SELECT bp,
       sp
FROM   BusinessPartner bp
       LEFT JOIN Person sp
              ON sp IN bp.salesPersons

In this case, the BusinessPartner (bp) resource, has a collection of persons named salesPersons.


4.7 Other Joins

Basically, we can use any expression for the joining condition.

SELECT ac1.id,
       ac1.endDateTime,
       ac2.id,
       ac2.endDateTime
FROM   Activity ac1
       JOIN Activity ac2
         ON ac1.endDateTime = ac2.endDateTime
WHERE  ac1 != ac2 

In this example, we ask for different Activity pairs (a1 , a2) that have the same endDateTime. Limitation: Internally, the query we write is transformed into a database query. Because of some database limitations, some joining conditions are not possible for RIGHT JOIN or FULL OUTER JOIN. When this is the case, we will get this error message:

{
   "error": "CA-135",
   "message": "Can not perform query, because of a database error: [{0}]",
   "values": [
      "ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions"
   ]
}

In this case we will have to rethink the joining condition.


5 The ORDER BY Clause

If we need to sort the results, we should use the ORDER BY clause. We can use multiple sorting criteria separated by a comma. If we don’t specify the sorting type (Ascending ASC or Descending DESC), by default, we will be using the Ascending sorting type.

SELECT a
FROM   Activity a
ORDER  BY a.lastChanged
SELECT a
FROM   Activity a
ORDER  BY a.type ASC,
          a.lastChanged DESC

6 Expressions

Expressions are used in most parts of a query: WHERE, JOIN, and/or ORDER BY clauses.

6.1 Supported Data Types

The following data types are supported by the Query API:

Name Description Example
TEXT Use simple quote for texts. 'abc'
'1234'
NUMBER A number can be: a integer or a real number. 15; -15; 10.2; -10.2
BOOLEAN we can mix lower cases with upper cases. true; false; TRUE; FALSE; trUE
DATE In operations that require DATE parameters,
TEXT or NUMBER tokens are converted to DATE.

For example: bp.lastChanged: '2015-02-15' or bp.lastChanged > 1429794685185
(Get all the Business Partners with the lastChanged greater than 2015-02-15)
(Get all the Business Partners with the lastChanged greater than the timestamp 1429794685185)

For a TEXT to be converted into a date, it should follow a pattern: yyyy-MM-dd'T'HH:mm:ss'Z'; yyyy-MM-dd; yyyyMMdd<
As text:
'2015-02-18T15:58:00Z'
'2015-02-18'
'20150218'

As timestamp:
1429794685185
REFERENCE
(In the next version, we will
rename this to IDENTIFIER)
By reference we mean object identifier (e.g. bp.id).
In operations that requires REFERENCE parameters,
TEXT tokens are converted to REFERENCE.

For example:
bp.id = '7C3A701C77194291B6CC0BEA817E4D36'

(Get the Business Partner with
the ID '7C3A701C77194291B6CC0BEA817E4D36')

Each time we use the resource name
(not its fields) into some expression,
it will be evaluated to a
REFERENCE type. For example:

bp = '7C3A701C77194291B6CC0BEA817E4D36' is the same as bp.id = '7C3A701C77194291B6CC0BEA817E4D36'

Each time we use the resource name (not its fields)
in a SELECT clause, it will be interpreted as
"give me all fields of this resource".
'7C3A701C 7719429 1B6CC0BEA 817E4D36'
REFERENCE_COLLECTION
(In the next version,
we will
rename this to
IDENTIFIER_COLLECTION)
This represents a collection of identifiers.
For example, the Business Partner has a field named groups.
This field stores a list of group identifiers.

This type can be used only in one operation: IN.
For example: We want to check if a Business Partner
belongs to some group with the identifier
'CA9EFA06A5F94EBD90CA26AE13B51DA5'.
SELECT bp FROM BusinessPartner bp,
Group g WHERE g.id IN bp.groups
AND g.id='CA9EFA06A5F94EBD90CA26AE13B51DA5'

//or... using the sugar syntax (in expressions, g=g.id)

SELECT bp FROM BusinessPartner bp,
Group g WHERE g IN bp.groups
AND g='CA9EFA06A5F94EBD90CA26AE13B51DA5'
bp.groups

6.2 Operations

We support the following operations:

Name Description Example
arg1 - arg2
arg1 + arg2
arg1 * arg2
arg1 / arg2
All those operations accept NUMBER arguments (arg1 and arg2).
For + operation, arg1 and arg2 can be also TEXT. When this is the case, the result will be a text concatenation.
1 + 2 = 3
'1' + '2' = '12'
arg1 = arg2
arg1 <= arg2
arg1 >= arg2
arg1 < arg2
arg1 > arg2
arg1 <> arg2
arg1 != arg2
All those operations work with TEXT, NUMBER and DATE arguments (arg1 and arg2).
The =, != and <> works also with REFERENCE and BOOLEAN arguments type.

The != and <> operations do the same thing. We support both, because some users prefer different syntax.

In operations where one of the arguments is a DATE and the other is a TEXT (or NUMBER), the TEXT (or NUMBER) will be converted to a DATE.

In operations where one of the arguments is a REFERENCE and the other is a TEXT, the TEXT will be converted to a REFERENCE.
10 = 10
'KX200' != bp.code

TEXT converted to a REFERENCE:
bp.id = '807E1D4F403643A2B7942DF98029B2CE'

NUMBER converted to a DATE:
bp.lastChanged != 1429794685185

TEXT converted to a DATE:
bp.lastChanged <> '2015-09-20'
arg1 AND arg2
arg1 OR arg2
NOT arg1
! arg1
NOT and ! operations do the same thing.

All those operations allow BOOLEAN arguments (arg1 and arg2).
true OR (1>5)
true or (1>5)

!(1>5)
not (1>5)
NOT (1>5)
arg1 IS NULL
arg1 IS NOT NULL
Checks if an argument is NULL or not.

arg1 can be of the type TEXT, DATE, NUMBER, BOOLEAN or REFERENCE.

Observation: Because of a known bug, resource.udfValues IS NULL or resource.udfValues IS NOT NULL will not work.
bp.code IS NULL
bp.code is null
bp.code IS NOT NULL
bp.code is not null
arg1 LIKE arg2
arg1 NOT LIKE arg2
arg1 ILIKE arg2
arg1 NOT ILIKE arg2
This operation allows we to check if a text (arg1) contains some subtext (arg2).
Both the arguments - arg1 and arg2 - should be of type TEXT.

arg2 can contain the symbol %. This is similar to SQL, it is a placeholder for "zero or more characters"

The difference between LIKE and ILIKE is that the first one is case sensitive and the other is not.
Suppose that bp.code='aBc', each of the expressions below is true:

bp.code LIKE 'aBc' = true
bp.code LIKE '%B%' = true
bp.code LIKE 'ABC' = false

bp.code ILIKE 'abc' = true
bp.code ILIKE 'AbC' = true
bp.code ILIKE 'ABC' = true
bp.code ILIKE 'YXZ' = false

bp.code NOT LIKE 'aBc' = false
bp.code NOT LIKE 'ABC' = true
bp.code NOT LIKE 'xyz = true

bp.code NOT ILIKE 'aBc' = false
bp.code NOT ILIKE 'xyz' = true
arg1 IN arg2
arg1 NOT IN arg2
The semantics of the IN operation is to check if a REFERENCE (arg1) is contained in some REFERENCE_COLLECTION (arg2). g IN bp.groups
g in bp.groups
g NOT IN bp.groups
g not in bp.groups
arg1 IN (arg2, arg3, ..., argN)
arg1 NOT IN (arg2, arg3, ..., argN)
The semantics of the IN operation is to check if an argument (arg1) is contained in some list (arg2,arg3,...,argN).

All the arguments in the list must be of the same type as arg1: NUMBER, TEXT, BOOLEAN, DATE or REFERENCE.
bp.code IN('abc','xyz','1234')
bp.code in('abc','xyz','1234')
bp.code NOT IN('abc','xyz','1234','baNAna')
bp.code not in('abc','xyz','1234','baNAna')
Any() ANY ( ) Returns a boolean value wherever a given array column contains the specified value boolean. Example Use Case
Get the information of an activity and the reponsible person linked to it (which is usually 1)
SELECT activity, person FROM Activity activity JOIN Person person ON person.id = ANY(activity.responsibles)
Get the information of the service call and linked reponsible person which could be more than 1 person resulting in several rows.
SELECT sc, person FROM ServiceCall sc, Person person WHERE sc.code = '1' AND person.id = ANY(sc.responsibles)
Get the information of the service call and linked equipment which could be more than 1 equipment resulting in several rows
SELECT sc.id, sc.code, sc.subject, sc.equipments, e.id, e.name, e.serialNumber FROM ServiceCall sc, Equipment e WHERE sc.code = '1' AND e.id = ANY(sc.equipments)
arg1 + arg2 For + operation, arg1 and arg2
can also be TEXT or DATE and TEXT or NUMBER and TEXT
Concatenates a string with other data types.
Example Description
1 + 2 = 3 Concatenation of numeric variables.
'1' + '2' = '12' Concatenation of strings.
'1' + 2 = '12' In this example, one of the values is a string '1' and the other is a number 2, in which case they are concatenated as if they were both strings. This is referred to as "implicit conversion".


Applied Use Case

The following is an example of how concatenation could be used in a real query:

SELECT DATEPART('DAY', a.createDateTime) + '-' + DATEPART('MONTH', a.createDateTime) + '-' + DATEPART('YEAR', a.createDateTime) from Activity a limit 5 => 3-11-2016 SELECT 'Create date time: ' + a.createDateTime from Activity a limit 5 => 'Create date time: 2016-11-03 13:41:37.194' SELECT LEFT(a.createDateTime + '', 10) from Activity a limit 5; => 2016-11-03

6.3 Regular Functions

The following functions are supported:

Name Description Example
LOWER(param1) It transforms a TEXT (param1) into its lowercase version. LOWER('aBc')='abc'
lower('aBc')='abc'
UPPER(param1) It transforms a TEXT parameter param1 into its uppercase version. UPPER('aBc')='ABC'
upper('aBc')='ABC'
NOW() It returns the current date and time. bp.lastChange < now()
COALESCE(param1, param2) Let's take an example: COALESCE(bp.code,'xyz')

What this function does: If it sees that bp.code is null, it will return the value 'xyz'. If bp.code is not null, it will return the value of bp.code.

Both parameters param1 and param2 should have the same type: TEXT, NUMBER, BOOLEAN or DATE.

If one of the parameters is a DATE and the second is TEXT (or NUMBER), then the TEXT (or NUMBER) parameter will be converted to a DATE.
Let's suppose that bp.code='xyz' and bp.lastChanged is null. Each of the expressions below is true:

                COALESCE(bp.code,'abc')='xyz'
                coalesce(bp.code,'abc')='xyz'
                
This returns a DATE representing 2015-01-20
coalesce(bp.lastChanged,'2015-01-20')
GETDATE(): TIMESTAMP Alias of NOW(). It returns the current date and time. o.lastChange < GETDATE()
DATEPART(param1: TEXT, param2: TIMESTAMP|INTERVAL): NUMBER Extracts the specified part (*param1*) of a timestamp (*param2*) and returns it as NUMBER value. Year
DATEPART('year', o.lastChange) >= 2016
Week
DATEPART('week', c.createDateTime) >= 26 (the week number 1/52 in which the dateTime occurred).

For example the following query: select c.createDateTime, DATEPART('week', c.createDateTime) from ChecklistTemplate c where c.id = '86745B6805DD480F9C09A4FC4F9E91AB'
would return 26 where c.createDateTime = "2017-06-28T11:14:15Z".
DAY(param1: TIMESTAMP|INTERVAL): NUMBER Alias of DATEPART('day', timestamp). Extracts the day part of a timestamp (*param1*) and returns it as NUMBER value. DAY(o.lastChange) = 1
MONTH(param1: TIMESTAMP|INTERVAL): NUMBER Alias of DATEPART('month', timestamp). Extracts the month part of a timestamp (*param1*) and returns it as NUMBER value. MONTH(o.lastChange) = 1
YEAR(param1: TIMESTAMP|INTERVAL): NUMBER Alias of DATEPART('year', timestamp). Extracts the specified part of a timestamp (*param1*) and returns it as NUMBER value. YEAR(o.lastChange) >= 2016
DATEADD(param1: TIMESTAMP, param2: TEXT|INTERVAL): TIMESTAMP Performs calculation on the given timestamp (*param1*) and returns a timestamp modified by the given interval (*param2*). DATEADD(o.lastChange, '1 year')
DATEADD(param1: INTERVAL, param2: TEXT|INTERVAL): INTERVAL Performs calculation on the given interval (*param1*) and returns a interval modified by the given interval (*param2*). DATEADD(o.lastChange, '1 year')
DATEINTIMEZONE(param1: TIMESTAMP, param2: TEXT): TIMESTAMP The first parameter is a timestamp you want to return in a different timezone, the second is the timezone identifier. Both formats are supported: 'Asia/Jakarta' or 'UTC+07:00'. (This is done by Postgres.) SELECT wt.startDateTime, DATEINTIMEZONE(wt.startDateTime, 'UTC+07:00') FROM WorkTime wt
SELECT wt.startDateTime, DATEINTIMEZONE(wt.startDateTi