VQL

The SQL-like query language for reading data across the whole ERP.

VQL (VARIO Query Language) is a SQL-like language for querying data across the entire VARIO Cloud ERP. It is the essential way to select and read data in apps and via the API. VQL lets you filter, sort, and paginate any entity — articles, accounts, documents, CRM activities, and beyond — with a single statement.

VQL is case-insensitive. All keywords — SELECT, select, Select — work identically.

---

Statement Structure

SELECT [DISTINCT] columns
FROM group.template
[JOIN (subquery) AS alias ON (condition)]
[WHERE expression]
[ORDER BY column [ASC|DESC], ...]
[LIMIT n]
[OFFSET n]

VQL supports JOIN but not LEFT, RIGHT, INNER, or OUTER variants. There is no GROUP BY, HAVING, or UNION.

## Columns

Select specific fields, use * for standard fields, or apply functions:

SELECT id, name, documentDate FROM document.querySalesDocuments

SELECT * FROM account.query

SELECT count(id) FROM article.query

Column Aliases

Use the --v:result{} annotation to control column behavior in the response:

SELECT
  --v:result{visible=false}
  id,
  --v:result{displayname='name'}
  default_address.name1
FROM account.query

Options:

Option	Type	Description
displayname	string	Key name in the returned data objects. Without it, the raw attribute path is used.
visible	boolean	Whether the column appears in UI renderings. Set to false to fetch data without displaying it.

Function	Description
COUNT(field)	Count
SUM(field)	Sum
AVG(field)	Average
MIN(field)	Minimum
MAX(field)	Maximum
ABS(field)	Absolute value
LIST(field)	List values

Functions can be nested: COUNT(SUM(field)).

Arithmetic (Available Soon)

Full arithmetic is supported in SELECT, WHERE, and ORDER BY:

select (quantity * unitPrice) from statistic.statisticSalesFact

Operators: +, -, *, /

---

FROM Clause

Every query targets a group and template in the format group.template:

SELECT * FROM article.query

SELECT * FROM document.querySalesDocuments

SELECT * FROM account.query

The group identifies the entity domain, the template defines which view of that data you query.

Don't guess field paths — ask the API

The set of groups, templates and field paths depends on your installation and changes between versions. Rather than looking a path up in documentation, enumerate it at runtime:

Endpoint	Returns
GET /cmn/computed-queries	All groups
GET /cmn/computed-queries/{group-key}	Templates in a group
GET /cmn/computed-queries/{group-key}/{id}/fields	Attributes of the template's root entity only — no relations
GET /cmn/computed-queries/fields	All entities with their attributes and relations — this is where you find joins and navigation paths
GET /cmn/computed-queries/dialect-info	The dialect's capabilities
POST /cmn/computed-queries/execute	Execute a VQL query

Looking for a path to a related entity? …/{id}/fields deliberately returns only the root entity's attributes. To find out what you can navigate or join to, use GET /cmn/computed-queries/fields — optionally narrowed with entityIdentifier (a fully qualified or simple entity name).

Useful query parameters on the field endpoints:

Parameter	Effect
filterable / resultable	Restrict to attributes usable in WHERE / in SELECT
verbose	Include detailed data-type information
visibility	Filter by attribute visibility
depth	How far to traverse (template fields endpoint)
entityIdentifier	Limit to one entity (all-fields endpoint)

This is the reliable answer to "what is the exact field path for …". A path you copied from a document may not exist in your version; a path you enumerated always does.

---

WHERE Clause

Operators

Operator	Example
=	WHERE status = 'ACTIVE'
!=	WHERE status != 'CLOSED'
> >= < <=	WHERE documentDate >= '2024-01-01'
LIKE	WHERE name LIKE '%GmbH%'
NOT LIKE	WHERE name NOT LIKE '%test%'
IN	WHERE id IN ('a1', 'a2', 'a3')
NOT IN	WHERE status NOT IN ('DRAFT', 'CANCELLED')
BETWEEN	WHERE amount BETWEEN 100 AND 500
NOTNULL / NOT NULL	WHERE externalId NOTNULL
ISNULL / IS NULL	WHERE deletedAt ISNULL
EXISTS (subquery)	WHERE EXISTS (SELECT id FROM ...)
NOT EXISTS (subquery)	WHERE NOT EXISTS (SELECT id FROM ...)
IN (subquery)	WHERE id IN (SELECT ... FROM ...)

Combine with AND, OR, and parentheses () for grouping.

Field-to-Field Comparison

WHERE modifiedDate > createdDate

---

Sorting and paging

ORDER BY, LIMIT and OFFSET work as they do in SQL, and they belong in the statement:

SELECT id, number, documentDate
  FROM document.querySalesDocuments
 ORDER BY id ASC
 LIMIT 10 OFFSET 20

Always order by a unique column when you page. Offset paging over an unsorted result set is undefined — pages overlap, so some rows arrive twice and others never arrive, differently on each run. id is the safe choice.

→ Pagination and filtering

LIMIT and OFFSET can also be supplied next to the statement — as limit and offset on the execute request, which is what the framework's vql() helper does. Both routes exist because tools such as JDBC/ODBC drivers add them independently of the statement text.

Don't set both. If a request field and a statement clause disagree, the request field wins and the clause in your statement is silently ignored. Pick one place — the statement when you write queries by hand, the parameters when you page programmatically.

---

Special Value Expressions

Relative Date — rd{}

Filter by dates relative to today. Format: rd{DAY.MONTH.YEAR} — each segment accepts * (keep current), an absolute value, or a relative offset (+N / -N).

Examples:

WHERE createdAt >= 'rd{*.-1.*}'

WHERE documentDate < 'rd{-14.*.*}'

Reference:

Expression	Meaning
rd{*.*.*}	Today
rd{-14.*.*}	14 days ago
rd{*.-1.*}	One month ago
rd{*.*.-1}	One year ago
rd{1.*.*}	First of the current month
rd{E.*.*}	First day of the current month
rd{L.*.*}	Last day of the current month
rd{EW.*.*}	First working day (Mon–Fri) of the current month
rd{LW.*.*}	Last working day (Mon–Fri) of the current month
rd{TODAY}	Today (alias)

Extends rd{} with a time component. Format: rdt{DAY.MONTH.YEAR HOUR:MINUTE} — the time part supports the same * / +N / -N syntax plus the keywords start, end, and now.

rd{} can also be used on DateTime fields — the system resolves it to a date and handles the conversion automatically. Use rdt{} only when you need explicit control over the time component.

Examples:

WHERE startDateTime > 'rdt{-14.*.* start}'

WHERE updatedAt >= 'rdt{*.-1.* 00:00}'

Reference:

Expression	Meaning
rdt{*.*.* now}	Current date and time
rdt{*.*.* start}	Start of today (00:00:00)
rdt{*.*.* end}	End of today (23:59:59)
rdt{-7.*.* 08:00}	7 days ago at 08:00
rdt{*.*.* *:*}	Current date and time (keep all)

An optional timezone suffix converts the result to UTC: rdt{*.*.* start;tz=Europe/Berlin}

Current User — env{me}

Resolves to the ID of the authenticated user. Use it to scope queries to the current user's data:

WHERE assignedUser.id = 'env{me}'

---

Custom Fields

Custom Fields (EAV attributes) are accessed via dot notation:

SELECT id, custom.myapp.externalId
FROM article.query
WHERE custom.myapp.externalId NOTNULL

Path pattern: custom.{groupKey}.{attributeKey}

---

Subqueries and JOINs

Subqueries

As a column (correlated — ^$ references the outer query):

SELECT (SELECT displayName FROM account.query WHERE id = ^$.addresses.account.id) AS accountName
FROM document.querySalesDocuments

As a FROM source:

SELECT t$.name FROM (SELECT name FROM account.query) AS t

In WHERE:

SELECT * FROM crm.tasks
WHERE NOT EXISTS (
  SELECT id FROM crm.task-activities
  WHERE ^$.id = relations.crmTask.id
    AND startDateTime > 'rd{-14.*.*}'
)

^$ references the parent query. alias$ references a named subquery or join.

JOIN

Joins use a subquery as the right-hand side:

SELECT
  id,
  number,
  docs$.totalNetPrice
FROM account.query
JOIN (
  SELECT sum(totalNetPrice), addresses.account.id, addresses.type
  FROM document.querySalesDocuments
) AS docs ON (
  docs$.addresses.account.id = id
  AND docs$.addresses.type = 'DEFAULT_ADDRESS'
  AND docs$.totalNetPrice > 0
)
WHERE docs$.addresses.account.id = id
LIMIT 1

Complex Example

This query finds all CRM tasks that had no activity in the past 14 days, are not finished, are older than 14 days, are in a specific state, and are assigned to the current user:

SELECT * FROM crm.tasks
WHERE NOT EXISTS (
  SELECT id FROM crm.task-activities
  WHERE ^$.id = relations.crmTask.id
    AND startDateTime > 'rd{-14.*.*}'
)
AND state.finishState = 'false'
AND createdAt < 'rd{-14.*.*}'
AND state.sortOrder = '55'
AND assignedUser.id = 'env{me}'
AND (assignedGroup.label != 'Group-Name' OR assignedGroup.id ISNULL)

---

App Framework Usage

VQL is the essential mechanism for selecting and reading data from the ERP within apps. Whether you need to look up a single record, fetch a filtered list, or aggregate values — app.erp.vql() is how your app queries the ERP:

const { data, moreElements, nextOffset } = await app.erp.vql({
  statement: `
    SELECT
      --v:result{displayname='id'}
      id,
      --v:result{displayname='name'}
      default_address.name1
    FROM account.query
    WHERE default_address.country.isoAlpha2 = 'DE'
    ORDER BY default_address.name1 ASC
  `,
  limit: 100,
  offset: 0,
});

Parameters

Parameter	Type	Default	Description
statement	string	—	The VQL query
limit	number|null	null	Max rows to return
offset	number|null	null	Rows to skip

Response

The response body contains:

Field	Type	Description
data	array	Result rows — keys match your displayname aliases
definition	array	Column metadata (data types, visibility, sortability)

Pagination

Pagination information is delivered via response headers. The framework reads them and exposes them on the response object:

Response Header	Framework Field	Type	Description
x-query-more-elements	moreElements	string	'true' if additional rows exist beyond the current page
x-query-next-offset	nextOffset	string	Pass this as offset in the next request to fetch the next page

Use limit and offset to page through results. When moreElements is 'true', pass nextOffset as the offset for the next call to continue.

moreElements and nextOffset are strings, not booleans or numbers. Always compare with === 'true' or === 'false'.

For the full Computed Query API specification, see the API Docs.