> For the complete documentation index, see [llms.txt](https://developer.vario-software.de/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.vario-software.de/documentation/fundamentals/vql.md).

# VQL

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

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

{% hint style="info" %}
VQL supports `JOIN` but not `LEFT`, `RIGHT`, `INNER`, or `OUTER` variants. There is no `GROUP BY`, `HAVING`, or `UNION`.
{% endhint %}

\## Columns

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

```sql
SELECT id, name, documentDate FROM document.querySalesDocuments
```

```sql
SELECT * FROM account.query
```

```sql
SELECT count(id) FROM article.query
```

### Column Aliases

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

```sql
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`:

```sql
select (quantity * unitPrice) from statistic.statisticSalesFact
```

Operators: `+`, `-`, `*`, `/`

***

## FROM Clause

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

```sql
SELECT * FROM article.query
```

```sql
SELECT * FROM document.querySalesDocuments
```

```sql
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                                                                                      |

{% hint style="info" %}
**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).
{% endhint %}

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

```sql
WHERE modifiedDate > createdDate
```

***

## Sorting and paging

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

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

{% hint style="warning" %}
**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](/documentation/rest-api/pagination-and-filtering.md)
{% endhint %}

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

{% hint style="info" %}
**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.
{% endhint %}

***

## 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:**

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

```sql
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`.

{% hint style="info" %}
`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.
{% endhint %}

**Examples:**

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

```sql
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:

```sql
WHERE assignedUser.id = 'env{me}'
```

***

## Custom Fields

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

```sql
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):

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

As a FROM source:

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

In WHERE:

```sql
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:

```sql
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:

```sql
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:

```javascript
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.

{% hint style="warning" %}
`moreElements` and `nextOffset` are **strings**, not booleans or numbers. Always compare with `=== 'true'` or `=== 'false'`.
{% endhint %}

{% hint style="info" %}
For the full Computed Query API specification, see the [API Docs](https://developer.vario-software.de/api-reference/computed-queries).
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://developer.vario-software.de/documentation/fundamentals/vql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.