> For the complete documentation index, see [llms.txt](https://academy.any2info.com/any2info-academy/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://academy.any2info.com/any2info-academy/no-code-platform/data-studio/data-hubs/toolbox/function/common/query.md).

# Query

**Category:** Import and export

**Version:** 1.0

**Last updated:** November 19, 2025

**Author:** Any2Info

***

### Description

The Query node allows you to execute SQLite queries against the output produced by the previous node in the data flow.\
All data passed from the preceding node is exposed through a virtual table named *Dataflow*, enabling you to further filter, transform, and select values as needed.

In addition to the Dataflow table, the node also provides access to any existing resultsets. Each resultset is stored internally as an SQLite table, allowing you to query them directly. This makes it possible to combine, join, or enrich the incoming data with data retrieved from these resultsets.

***

### SQLite

Since the datahub operates locally—and may even run as an edge device—it must use a lightweight, embedded database engine to execute queries directly on the device. For this purpose, the datahub uses SQLite, which provides fast, serverless data processing with minimal resource requirements.

Because SQLite follows its own SQL dialect, some syntax and functions differ from systems such as SQL Server. As a result, certain SQL expressions may behave differently than users expect. Even so, SQLite offers a powerful and efficient environment for the filtering, transformation, and data-combination tasks performed within the Data Hub.

***

### SQLite functions

SQLite supports a wide range of functions and SQL constructs that can be used to filter, and transform data within the datahub.

Below is an overview of commonly used SQLite functions and syntax elements that are relevant when writing queries in the Query node.

**1. Selection and Limiting**

**LIMIT**\
• Retrieves a specific number of rows (SQLite’s equivalent of TOP).\
• Example: SELECT \* FROM table LIMIT 10

**LIMIT … OFFSET**\
• Used for paging (skip a number of rows, then return the next set).\
• Example: LIMIT 10 OFFSET 20

\
**2. String Operations**

**||**\
• Concatenates two or more strings.\
• Example: firstName || ' ' || lastName

**substr(column, start, length)**\
• Returns a substring starting at position 1.\
• Example: substr(name, 1, 3)

**length(column)**\
• Returns the number of characters in a string.

**upper(column)**\
• Converts a string to uppercase.

**lower(column)**\
• Converts a string to lowercase.

**TRIM(column)**\
• Removes leading and trailing whitespace.

**LTRIM(column)**\
• Removes whitespace from the left side of the string.

**RTRIM(column)**\
• Removes whitespace from the right side of the string.

**TRIM(column, 'x')**\
• Removes a specific character from both sides.\
• Example: TRIM(column, '.')

 

**3. Joins**

**INNER JOIN**\
• Returns rows that exist in both tables.\
• Example: SELECT … FROM A JOIN B ON A.id = B.fk

**LEFT JOIN**\
• Returns all rows from the left table and matching rows from the right table.\
• Example: SELECT … FROM A LEFT JOIN B ON A.id = B.fk

**Not supported in SQLite**\
• RIGHT JOIN\
• FULL OUTER JOIN\
• CROSS APPLY / OUTER APPLY

 

**4. Type Conversions**

**CAST(column AS INTEGER)**\
• Converts a value to an integer.

**CAST(column AS REAL)**\
• Converts a value to a floating-point number (double).

**CAST(column AS TEXT)**\
• Converts a value to a string.

\
**5. Date and Time Functions**

**date('now')**\
• Returns the current date (YYYY-MM-DD).

**time('now')**\
• Returns the current time (HH:MM:SS).

**datetime('now')**\
• Returns the current date and time.

**strftime(format, value)**\
• Formats a date or time value.\
• Example: strftime('%Y-%m-%d', 'now')

**date('now', '+x days')**\
• Adds or subtracts days.\
• Example: date('now', '+7 days')

**datetime('now', '-x hours')**\
• Adds or subtracts hours.\
• Example: datetime('now', '-1 hour')

\
**6. Logical Operations and NULL Handling**

**CASE WHEN … THEN … END**\
• Conditional logic for branching.

**IFNULL(value, fallback)**\
• Returns the fallback value if the first value is NULL (similar to ISNULL in SQL Server).

**COALESCE(a, b, …)**\
• Returns the first non-NULL value from the list.

 

**7. Collations and Case Sensitivity**

**COLLATE NOCASE**\
• Performs case-insensitive comparison.\
• Example: column = 'abc' COLLATE NOCASE

**COLLATE BINARY**\
• Performs case-sensitive comparison.\
• Example: column = 'abc' COLLATE BINARY

***

### Context tables

In addition to the standard **\[DataFlow]** table, the Query node provides access to two special context tables:

* **\[DataFlow\.Context]**
* **\[DataFlow\.Nodes]**

These tables expose information about the current DataFlow session and can be used for auditing, monitoring, logging, reporting, or creating runtime summaries directly within a query.

The context tables are only created when they are referenced in the query. The current Query node is excluded from the context information. As a result, these tables only contain information about nodes that have already been executed within the current session.

***

**DataFlow\.Context**

The **\[DataFlow\.Context]** table contains a single row with summary information about the current DataFlow session.

| Column       | Description                                                                        |
| ------------ | ---------------------------------------------------------------------------------- |
| SessionId    | Unique identifier of the current DataFlow session.                                 |
| DataFlowId   | Identifier of the DataFlow, if available.                                          |
| CollectionId | Identifier of the collection in which the DataFlow is running.                     |
| Name         | Name or description of the DataFlow execution.                                     |
| Triggered    | Indicates how the flow was started.                                                |
| Started      | Date and time when the DataFlow session started.                                   |
| NodeCount    | Number of executed nodes in the current session, excluding the current Query node. |
| WarningCount | Total number of warnings logged so far, excluding the current Query node.          |
| ErrorCount   | Total number of errors logged so far, excluding the current Query node.            |

**Example**

```sql
SELECT *
FROM [DataFlow.Context]
```

Combining runtime information with the current data:

```sql
SELECT d.*, c.SessionId, c.Started
FROM [DataFlow] d
CROSS JOIN [DataFlow.Context] c
```

***

**DataFlow\.Nodes**

The **\[DataFlow\.Nodes]** table contains one row for each node that has already been executed in the current DataFlow session.

| Column       | Description                                             |
| ------------ | ------------------------------------------------------- |
| NodeId       | Unique identifier of the node execution.                |
| Parent       | Identifier of the parent node. Empty for the root node. |
| Name         | Display name of the node as shown in the execution log. |
| ExtensionId  | (GUID) of the executed DataFlow function.               |
| Started      | Date and time when the node started execution.          |
| Finished     | Date and time when the node finished execution.         |
| RowCount     | Number of output rows produced by the node.             |
| WarningCount | Number of warnings logged for the node.                 |
| ErrorCount   | Number of errors logged for the node.                   |

**Example**

```sql
SELECT Name, RowCount, WarningCount, ErrorCount
FROM [DataFlow.Nodes]
ORDER BY Started
```

Runtime summary example:

```sql
SELECT
    c.Name AS DataFlowName,
    c.Started,
    c.NodeCount,
    c.WarningCount,
    c.ErrorCount,
    n.Name AS NodeName,
    n.RowCount
FROM [DataFlow.Context] c
LEFT JOIN [DataFlow.Nodes] n ON 1 = 1
ORDER BY n.Started
```

***

**Practical Example: Continue Processing Only When No Errors Have Occurred**

The context tables can be used to build conditional logic within a DataFlow.

For example, the following query returns all rows from the current **\[DataFlow]** table only when no errors have been logged by previously executed nodes:

```sql
SELECT d.*
FROM [DataFlow] d
CROSS JOIN [DataFlow.Context] c
WHERE c.ErrorCount = 0
```

If one or more errors have occurred earlier in the DataFlow, the query returns no rows.

This can be combined with the **Has Rows** precondition on a subsequent node. The next node will only execute when all previously executed nodes completed without errors, providing a simple mechanism to stop processing when a failure has occurred earlier in the DataFlow.

***

### Tips & Best Practices

* Be aware of SQLite-specific syntax.\
  Some SQL Server features (such as RIGHT JOIN or FULL OUTER JOIN) are not available, and certain functions behave differently.
* Use the Dataflow table as the primary input for your query, and only reference result sets when needed.

***

### Errors & Troubleshooting

• Syntax differences from SQL Server.
\
Errors may appear if SQL Server syntax is used. Features like RIGHT JOIN, FULL OUTER JOIN, or certain functions are not supported in SQLite.

***

### Changelog

<table><thead><tr><th width="131">Version</th><th>Date</th><th>Change</th></tr></thead><tbody><tr><td><strong>1.0</strong></td><td>December 4, 2025</td><td>Initial documentation version added.</td></tr><tr><td><strong>1.1</strong></td><td>June 8, 2026</td><td><p>Added support for the system tables:</p><ul><li>dataflow.context</li><li>dataflow.nodes</li></ul></td></tr></tbody></table>


---

# 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://academy.any2info.com/any2info-academy/no-code-platform/data-studio/data-hubs/toolbox/function/common/query.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.