6 Query API

Source: This article refers to 6 Query API
Warning: Ignore this page and refer to the following topic if you plan to use the query API without username and password and log-in using federated authentication instead.

6.1 Making an API Call

The Query API works with POST HTTP requests. A server call should follow the template:

Request parameter Description Example
{dataCloudURL} The URL of the Data Cloud server {node}.dc.coresuite.com
{accountName} The account name used during the account registration myAccountName
{userAccountName} The user account name (received by email during the account registration) myUserName
{userAccountPassword} The user account password (please read the observation below) myPassword
{companyName} The company name used during the account registration myCompanyName
{clientIdentifier} The application's identifier that is doing the API call COR_CONNECTOR
{dtoVersions} The version of the resources that we will query for.

Please note how this text is constructed:
Resource1.Version;Resource2.Version;....;ResourceN.Version

Please check the Data Model section.
BusinessPartner.17;ServiceCall.17
{selectStatement} The Sql-Like statement we want to execute. SELECT bp.id, bp.name, sc.id FROM BusinessPartner bp JOIN ServiceCall sc ON bp=sc.businessPartner LIMIT 3

Observation: Besides using the userAccountPassword parameter, we can also use a more secure SHA-512 hash format: userAccountPasswordHash.
https://{dataCloudURL}/api/query/v1?
....
userAccountPasswordHash=d4decdde939c78532cbcf1948465bd4dfb338901fb83fb615773b3b630ff45e2209083249995c5c1e720a1c71649a6c53672dc8f52ebc01d352ffc949a4bfe87 &
....

Example

We want to get the first three pairs of (Business Partner , Service Call). In each pair there is this relation:

The Service Call is connected to its Business Partner by bp=sc.businessPartner


Request

POST https://et.dev.coresuite.com/api/query/v1?&account=scribe&company=Ambit%20AG&clientIdentifier=COR_CON_NONE?dtos=BusinessPartner.17;ServiceCall.17

POST request body (application/json)

{"query":"SELECT bp.id, bp.name, sc.id FROM BusinessPartner bp JOIN ServiceCall sc ON bp=sc.businessPartner LIMIT 3"}


6.2 The SELECT Clause

By using this API, we query for resources in an SQL manner, similar to querying in database tables.

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 JOINs 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.

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

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


6.2.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


6.3 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.


6.4 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 '%TEC%'  AND bp.lastChanged > '2014-01-01' AND ( bp.creditLimit.amount + 10 >= 15 AND bp.creditLimit.currency='CHF')

For more filtering possibilities, please visit the section Expressions and Examples


6.5 The JOIN Clause

According to the joining possibilities, we support four join types:


6.5.1 JOIN (or INNER JOIN)

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)


6.5.2 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

6.5.3 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

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


6.5.5 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).


6.5.6 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).


6.5.7 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.


6.5.8 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.


6.6 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.7 Expressions

Expressions are used in most parts of a query: WHERE, JOINs, ORDER BY clauses.

What data types are supported in the Query API?

6.7.1 Data Types

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'</li>
  • 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.7.2 Operations

We support the operations below:

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.7.3 Regular Functions

We support the functions below:

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'<br/>
                coalesce(bp.code,'abc')='xyz'<br/><br/>
                
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')
DATEDIFF(timeUnit: ENUM, startDateTime: TIMESTAMP, endDateTime: TIMESTAMP): NUMBER Calculates difference between two timestamps in the given time unit.

Supported time unit enums: (day, dd; minute, mi)
DATEDIFF(day, o.startDateTime, o.endDateTime)
LEFT() Returns the left part of a character string with the specified number of characters. LEFT(sc.subject, 2)
RIGHT() Returns the right part of a character string with the specified number of characters. RIGHT(sc.subject, 3)
REPLACE() Replaces all occurrences of a specified string value with another string value. REPLACE(sc.subject, 'test', 'funny')
LEN() / LENGTH() Returns the number of characters of the specified string expression, excluding trailing blanks.
LTRIM() Removes the space character char(32) or other specified characters located to the left of the string.
RTRIM() Removes the space character char(32) or other specified characters located to the right of the string.
TRIM() Removes the space character char(32) or other specified characters from the start and end of the string.
REVERSE() Returns the reverse order of a string value.

6.7.4 Aggregate Functions

Those functions are similar with SQL aggregate functions.

Any field that is not part of the aggregate function parameter, must be specified into the GROUP BY clause. Any restrictions on the aggregate function result must be specified into the HAVING clause.

We support the functions below:

Name Description Example
AVG(param1) This returns the average value for param1 for all the result tuples that match the grouping condition.

The value of parameter param1 can be an expression which evaluates to a NUMBER.
SELECT so.code,avg(so.potentialAmount.amount) FROM SalesOpportunity so GROUP BY so.code 


The result in object format:
{ "data": [ { "so": { "code": "8" }, "avg(so.potentialAmount.amount)": 100000 }, { "so": { "code": "48" }, "avg(so.potentialAmount.amount)": 30000 } ] } 
AVG( DISTINCT param1) This returns the average value for param1 for all the result tuples that match the grouping condition.

. The average value is calculated based on the DISTINCT values of parameter param1.

The value of parameter param1 can be an expression which evaluates to a NUMBER.
SELECT so.code,avg(distinct so.potentialAmount.amount) FROM SalesOpportunity so GROUP BY so.code

The result in tabular format:
{ "data": [ { "so.code": "1", "avg(distinct so.potentialAmount.amount)": 29005 }, { "so.code": "10", "avg(distinct so.potentialAmount.amount)": 60000 } ] } 
COUNT(param1) This counts the number of result tuples that match the grouping condition. The counting take place for non-null values of the param1.

Counting on null values of the param1 will always return 0.
SELECT bp.code, count(bp.id) as result FROM BusinessPartner bp GROUP BY bp.code HAVING result=1

Here we use an alias for referencing the function.
The result in tabular format:
{ "data": [ { "bp.code": "C000151", "result": 1 }, { "bp.code": "C000142", "result": 1 } ] } 
COUNT( DISTINCT param1) This counts the number of result tuples that match the grouping condition. The counting take place for non-null and DISTINCT values of the param1.

Counting on null values of the param1 will always return 0.
  SELECT bp.city, count(distinct bp.code) FROM BusinessPartner bp GROUP BY bp.city 
COUNT(*) This counts the number of result tuples that match the grouping condition.
 SELECT bp.city, count(*) FROM BusinessPartner bp GROUP BY bp.city HAVING count(*) 4 ORDER BY count(*) DESC 
MAX(param1) This gets the maximum value of the parameter param1 by inspecting all the result tuples that match the grouping condition.

The value of parameter param1 can be an expression which evaluates to a: NUMBER, TEXT or DATE.
SELECT e.item, max(e.name) FROM Equipment e GROUP BY e.item 


The result in object format:
{ "data": [ { "e": { "item": "2CFD47B14F6642168A07E71322F2290E" }, "max(e.name)": "HP 600 Series Tinte" }, { "e": { "item": "A1AC99D31B9F4438881B36B8638C5356" }, "max(e.name)": "Server Point 10000" } ] } 
MIN(param1) This gets the minimum value of the parameter param1 by inspecting all the result tuples that match the grouping condition.

The value of parameter param1 can be an expression which evaluates to a: NUMBER, TEXT or DATE.
 SELECT e.item, min(e.name) FROM Equipment e GROUP BY e.item 
SUM(param1) This returns the sum for all the values of the parameter param1 by inspecting all the result tuples that match the grouping condition.

The value of parameter param1 can be an expression which evaluates to a NUMBER.
  SELECT sum(so.potentialAmount.amount) FROM SalesOpportunity 
SUM
( DISTINCT param1)
This returns the sum for all the DISTINCT values of the parameter param1 by inspecting all the result tuples that match the grouping condition.

The value of parameter param1 can be an expression which evaluates to a NUMBER.
 SELECT sum(distinct so.potentialAmount.amount) FROM SalesOpportunity so 

6.7.5 Expression Alias

You can define expressions in the SELECT clause by using aliases. Later, you can use those aliases into WHERE, HAVING and ORDER BY clauses.

Let’s look at the following example:

SELECT so.code,
       sum(so.potentialAmount.amount),
       sum(so.potentialAmount.amount) + 1 as sumPlusOne,
       LOWER(so.potentialAmount.currency) + '_suffix' as lowerCurrencyWithSuffix
FROM SalesOpportunity so
WHERE lowerCurrencyWithSuffix='chf_suffix'
GROUP BY so.code, so.potentialAmount.currency
HAVING sumPlusOne>10
ORDER BY sumPlusOne DESC
LIMIT 2

This returns the output (in object format):

In this example we use two aliases: sumPlusOne (for the expression sum(so.potentialAmount.amount) + 1) and lowerCurrencyWithSuffix (for the expression LOWER(so.potentialAmount.currency) + ‘_sufix’).

Note: Do not use reserved words as aliases!

For example you can NOT use:

  • names of the functions: sum, lower, max...
  • operations names: and, or, in...
  • names of SQL clauses: select, from, where, on...

6.7.5.1 Alias Expression in GROUP BY Clause

Please note that Alias in the GROUP BY clause is NOT supported.

This means that the following query does NOT work:

select p.firstName + ' ' + p.lastName as FullName, count(sa.id) from Person p  join ServiceAssignment sa on sa.technician = p.id GROUP BY FullName 

While the following query DOES work

select p.firstName + ' ' + p.lastName as FullName, count(sa.id) from Person p  join ServiceAssignment sa on sa.technician = p.id GROUP BY p.firstName, p.lastName

6.8 OFFSET and/or LIMIT

Sometimes we don’t want to get all the results, but only some of them: a page.

(If the result set is a large one, it is also recommended not to get all the results at once).

For getting only a page we have two options: pagination or OFFSET and/or LIMIT.

(we cannot use pagination combined with OFFSET and/or LIMIT)

OFFSET and LIMIT is discussed here.

SELECT a.id FROM Activity a OFFSET 5 LIMIT 3
SELECT a.id FROM Activity a OFFSET 5
SELECT a.id FROM Activity a LIMIT 3

First, please note that the OFFSET and LIMIT are placed at the end of the query!

Second, we are not forced to use both at the same time. But if we want to use both, the order should be respected: first OFFSET then LIMIT.

For understanding OFFSET and LIMIT, we will take the next example:

SELECT a.id FROM Activity a

Suppose this query returns 1,000,000 Activities (actually 1,000,000 pairs. In this case a pair has only one resource: an Activity).

Because there are too many results, we are interested only in displaying 100 results (this is LIMIT), starting from the position 500 (this is OFFSET) in the result set.

For doing this, we will use:

SELECT a.id FROM Activity a OFFSET 500 LIMIT 100

6.9 UNION

The UNION operator to join two or more queries together.

Example

select a.id as id from Activity union select bp.id as id from BusinessPartner bp
(select a.id as id from Activity) union (select bp.id as id from BusinessPartner bp)

An individual query from the composite UNION query can contain anything, as well as subqueries. Individual queries need to contain same number of columns.

Usage

  • When making the call via Query API and using composite UNION queries, you only need to provide DTOs for the first individual query tables.
  • UNION queries only return distinct non-repeating results.
  • It is not possible to using UNION in subqueries, this would not be simple to do and subqueries currently do not support more than one table anyway.
  • Currently, individual queries may contain ORDER BY. However, it is not currently possible to use ORDER BY for the whole composite query. Note that currently this query:
select a.id as id from Activity union select bp.id as id from BusinessPartner bp order by id 

will return ids from the Activity table in no order followed by ids from BusinessPartner table, which will be returned in order. ORDER BY is only possible on the composite query in case of a UNION query, the ORDER BY in the example query will sort ids from Activity as well.


6.10 Pagination

Let’s take again the previous example. So we do the query:

SELECT a.id FROM Activity a

This will return 19,484 Activities.

The new requirement is: we want to see the results page by page. This is what the pagination concept addresses.

We must define two things: the page we want to get and how many pairs we see on a page, the page size. (both must be natural numbers greater then zero)

If we want the page number 10 (of the total of 6,495 pages), having 3 Activities in a page, then we have to build our call like this:

This time, the query response contains the pagination information:

  • The pageSize represents the number of pairs shown in a page;
  • The currentPage represents the index of the page we got (10 of the total of 6,495 pages);
  • The lastPage represents the index of the last available page;
  • The totalObjectCount represents the number of pairs (only Activities in this case) that the query returns if no pagination were used.


One more thing: we cannot use pagination in combination with OFFSET and/or LIMIT. Because, behind the scenes, pagination introduces its own OFFSET/LIMIT in the query.


6.11 User Defined Fields (UDFs)

There is a conceptual difference between a system (“real”) property (like Business Partner’s name) and a user-defined field (“virtual”) property (like SWA_PI_EBill).

To exemplify, let’s try this query:

SELECT bp.name, bp.udf.SWA_PI_EBill FROM BusinessPartner bp LIMIT 1

First, we can see that the UDFs are displayed in a list (udfValues).

Second, a UDF contains three elements: a meta, a value and a name. So what is the type of this UDF?

There is more information that we can get related to a UDF, but it is not stored in the resource itself (Business Partner, in this case). It is stored in a special resource: UdfMeta.

The connection between this UDF value (stored in Business Partner) and the corresponding UdfMeta resource is made by the meta field. (“meta”: “84D08597D70A4109894CB7501B6081B1”).

Let’s take a look to the UdfMeta of this virtual field: SWA_PI_EBill.

SELECT um FROM UdfMeta um WHERE um.id='84D08597D70A4109894CB7501B6081B1'

We will get (some null fields were removed from this response):

And finally, we managed to find the type of the UDF in the example: SELECTIONLIST.

Therefore, there is a bit more information to consider when working with UDFs:

  • They can be added to a resource type, but later removed.

Imagine that today we create a new UDF and link it to the BusinessPartner resource type. Suppose then we use this UDF for different reports.After 2 weeks, it gets removed from the system.

The question arises: What happens to those reports that use the undefined UDF? If this UDF is used only inSELECTclauses, it is simply ignored. It will not be included in the **udfValues** list. If this UDF is used in anyWHERE,JOINorORDER`` BY clauses however, then no assumptions are made about the undefined UDF when evaluating the expressions. For such cases, the error is exposed and the responsibility is passed to the API user.

We will get this error:

(When constructing the error handling is advisable to be careful: in the future the error messages can change, but not the error codes (CA-152)! Building the error handling based on error codes is highly advisable).

A UDF can have the same name as a system field. For example:

Because of this, a special construct is used to make the difference: An UDF can be queried by using the syntax: resource.udf.name (for example bp.udf.SWA_PI_EBill) There are also other ways to access an UDF. The following queries are equivalent:

  //get by UDF name
  SELECT bp.udf.CcEmail FROM BusinessPartner bp WHERE bp.udf.CcEmail='csstext@email.ch;ovidiu@email.ch'

  //by using a function
  SELECT getUdfByName(bp,'CcEmail') FROM BusinessPartner bp WHERE getUdfByName(bp,'CcEmail')='csstext@email.ch;ovidiu@email.ch'

  //get by UDF meta
  SELECT getUdfByMeta(bp,'58186B814E8C41FAAA74D303C809C633') FROM BusinessPartner bp WHERE getUdfByMeta(bp,'58186B814E8C41FAAA74D303C809C633')='csstext@email.ch;ovidiu@email.ch'
  

The output will be:


6.12 Error Handling

The Query API has a rich set of error messages that will help construct a correct query.

There are two types of errors:

  • Shown during the query build process.

These errors will guide the construct of a correct syntactic query. Most of the errors fall into this category.

  • Blockers. Shown at a later time (after the query has been running for a while).

These errors are the most relevant. Let’s take a scenario:

  • We want to build a report based on some UDFs evaluation. UDFs can be dynamically added and removed over time.
  • Suppose that we build the report using an existing UDF.
  • After 2 months, the UDF gets removed from the system (company).
  • Now, the same report that used to work before will no longer work. Instead, we will receive an exception message saying:
 { "error": "CA-152", "message": "There is no Udf [{0}] in UdfMeta associated with the resource [{1}].", "values": [ "bp.udf.code", "bp" ] } 

To answer “How many of those blocker errors are there?”, the answer is ONE: code CA-152. So error handling should be built only for this error code CA-152. For example: when we get a CA-152 error code, we can choose not to show the report any longer, or show a message.

We encourage the usage of an error handling mechanism when building reports!

When errors occur, please consider taking action based on the error codes received.

It is not advisable to build error handling mechanisms base on the text messages that follow the error codes! Those text messages can change in future releases. The error codes, however, will not.


6.13 Output Formats

We support two output formats: object (this this the default) and tabular.

To select the output format, you may have to use the optional parameter: outputFormat (having the values: object or tabular). This parameter is described here.

In the output result, the resources are grouped in tuples:

The differences between those two formats is made in the way the resource fields are structured.

Lets run the query bellow and see the differences between those two output formats.

SELECT bp.id, bp.code,bp.syncObjectKPIs,sc.id,sc.responsibles,sc.technicians
FROM BusinessPartner bp JOIN ServiceCall sc ON bp=sc.businessPartner  LIMIT 1

The result in object format looks like:

The same result in tabular format looks like:


6.14 More Examples

  • Getting all the fields from all Business Partners:
SELECT bp FROM BusinessPartner bp

  • Getting only the fields ID, country, type, UDF Value from all Business Partners:
SELECT bp.id, bp.country, bp.type, bp.udfValues FROM BusinessPartner bp

  • Getting only the fields id, and SWA_PI_EBill (this is a UDF) from all Business Partners:
SELECT bp.id, bp.udf.SWA_PI_EBill FROM BusinessPartner bp

  • Joining all Business Partners and their Service Calls. Pairs (Business Partner, Service Call):
SELECT bp, sc FROM BusinessPartner bp JOIN ServiceCall sc ON bp=sc.businessPartner
SELECT bp, sc FROM BusinessPartner bp JOIN ServiceCall sc ON bp.id=sc.businessPartner  // we use the syntax bp.id

  • Joining all Business Partners and their Activities. Pairs (Business Partner, Activity):
SELECT bp, a FROM BusinessPartner bp JOIN Activity a ON bp=a.object
SELECT bp, a FROM BusinessPartner bp JOIN Activity a ON bp.id=a.object.objectId // here we use the long syntax

  • Getting only the Business Partners with the currency ‘EUR’:
SELECT bp FROM BusinessPartner bp WHERE bp.creditLimit.currency='EUR'

  • Getting only the Business Partners (fields: id and name) with the ID from a list of texts (representing identifiers):
SELECT bp.id,bp.name FROM BusinessPartner bp WHERE bp IN ('D5C32244D7AC4F0380EDEC002E59447F','40EE5FCAF9FC48A3AC701F092C9E6DB9')
SELECT bp.id,bp.name FROM BusinessPartner bp WHERE bp.id IN ('D5C32244D7AC4F0380EDEC002E59447F','40EE5FCAF9FC48A3AC701F092C9E6DB9') //long syntax bp.id

  • Getting only the Business Partners (fields: id and lastChanged) with the lastChanged equal to 1429794685185 or 1429794686369 timestamps:
SELECT bp.id, bp.lastChanged FROM BusinessPartner bp WHERE bp.lastChanged IN (1429794685185, 1429794686369)

//or
SELECT bp.id, bp.lastChanged FROM BusinessPartner bp WHERE bp.lastChanged=1429794685185 or bp.lastChanged=1429794686369

  • Getting only the Business Partners (fields: id and lastChanged) with the lastChanged lower than 2016-02-29T09:07:12Z
//or using the format yyyy-MM-ddThh:mm:ssZ
SELECT bp.id, bp.lastChanged FROM BusinessPartner bp WHERE bp.lastChanged<'2016-02-29T09:07:12Z'

//or using the format yyyy-MM-dd
SELECT bp.id, bp.lastChanged FROM BusinessPartner bp WHERE bp.lastChanged<'2016-02-29'

//or using the format yyyyMMdd
SELECT bp.id, bp.lastChanged FROM BusinessPartner bp WHERE bp.lastChanged<'20160229'

6.15 Using Custom Objects / Fields in Queries

The following is an example of a custom object/field referenced in a query. Custom Objects and Fields are created and defined in Admin > Company > Custom Objects.

SELECT locationHistoryDefinition.name, locationHistoryDefinition.description
, locationHistory.createDateTime, locationHistory.createPerson
, locationHistory.udf.code, locationHistory.udf.dateTime, locationHistory.udf.technician, locationHistory.udf.equipment, locationHistory.udf.latitude, locationHistory.udf.longitude
FROM UdoValue locationHistory
JOIN UdoMeta locationHistoryDefinition ON locationHistoryDefinition.id = locationHistory.meta
WHERE locationHistoryDefinition.name = 'LocationHistory';
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.