--- title: "Execute an SQL statement" method: POST path: "/reference-data/v1/sql/execute" tags: ["Reference data"] --- # Execute an SQL statement `POST /reference-data/v1/sql/execute` ## Request - Base URL: `https://your-environment.ataccama.one/api` - URL: `https://your-environment.ataccama.one/api/reference-data/v1/sql/execute` - Auth: HTTP bearer - TypeScript: (a function with typed inputs; its response is checked against the schema) Read published reference data with an SQL `SELECT` statement. Unlike the per-table filter endpoints, one statement can read several tables and join them on a condition of your choosing. The `statement` field describes the currently supported SQL. This endpoint only reads data and never modifies it. Only `SELECT` statements are accepted. A statement reads the latest published version of each table it names. All tables are read from one consistent snapshot, even if a publish completes while the statement runs. The response contains either the complete result or an error. Results are never truncated. A response holds at most 10000 rows: if the result would be larger, the statement is rejected, so add a `LIMIT` of 10000 or fewer, or narrow the statement. A `LIMIT` above 10000 is rejected before the statement runs, even if the statement would have returned fewer rows than that. A statement is also rejected if the response, or a single value in it, would be too large to return, or if the statement runs too long. Check `meta.next` to see whether the response holds the whole result: when it is present, the result continues beyond this response. A result that does not fit within these limits is rejected rather than split across responses, so narrow the statement instead. `meta.total` is not returned. A problem with the statement itself returns `400 Bad Request`: invalid syntax, an unknown column, an unsupported construct, an evaluation error such as division by zero or a failed cast, a timeout, or a result that exceeds a size limit. The `detail` field explains the reason and, where applicable, names the size limit or the line and column in the statement. `detail` is a human-readable message: its wording can change, and no field in the response identifies the specific cause. Your API client needs read permission on every table the statement names. If the request has no valid token, or the statement names a table you cannot read, the whole statement fails with `403 Forbidden`. A table that does not exist returns the same `403` with the same `detail` as a table you cannot read, so the two cases are intentionally indistinguishable. Do not treat them differently. ## Request body - SqlStatementInput - `statement` string, required: An SQL `SELECT` statement over published reference data. **Names.** Refer to tables by their reference data table name and to columns by their attribute name. Both are matched case-insensitively. A result column is named by its alias, exactly as written. A result column without an alias takes the attribute's own name, not the spelling used in the statement. If a table is renamed, the statements that read it stop working. **Supported syntax** ``` SELECT [, ]... FROM [[AS] ] [ [INNER | {LEFT | RIGHT | FULL} [OUTER]] JOIN
[[AS] ] ON ]... [ WHERE ] [ ORDER BY [ASC | DESC] [, ...] ] [ LIMIT ] ``` A select item is a column or a scalar expression over columns and literals. A computed item must have an alias (`AS`); an alias on a plain column is optional. Result column names must be unique, compared case-insensitively, so alias at least one of two columns that would otherwise get the same name. Every result column must have a type, so a bare `NULL` is rejected; cast it instead, for example `CAST(NULL AS VARCHAR)`. `ORDER BY` accepts a result column by name or by position, or any expression that could be a select item, including a column that is read but not selected. If a column name exists in more than one table in the statement, qualify it with the name or alias of its table. **Reference attributes.** An attribute that references another table reads as the value of the attribute it points to in that table, with that attribute's data type. `meta.columns` therefore reports it as `STRING`, or whatever the referenced attribute's type is, never as an identifier such as a URN. This is the same value the record endpoints return for the attribute. Otherwise it behaves like any other attribute, and a reference that points to no record reads as null. **Expressions.** An expression can use: - the comparison operators `=`, `<>`, `<`, `<=`, `>`, `>=` - the predicates `IS NULL`, `IS NOT NULL`, `IN`, `NOT IN`, `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE`, each `LIKE` form optionally followed by `ESCAPE ''` - the Boolean operators `AND`, `OR`, `NOT` - the arithmetic operators `+`, `-`, `*`, `/`, and unary `+` and `-` - the concatenation operator `||` - `CASE` expressions - the functions `CHARACTER_LENGTH`, `SUBSTRING`, `POSITION`, `UPPER`, `LOWER`, `TRIM`, `CONCAT`, `OVERLAY`, `GREATEST`, `LEAST`, `LPAD`, `RPAD`, `ABS`, `MOD`, `CEIL`, `FLOOR`, `ROUND`, `TRUNCATE`, `POWER`, `SQRT`, `EXP`, `SIGN`, `COALESCE`, `NULLIF`, `REGEXP_REPLACE`, and `CAST` `CAST` supports the target types `VARCHAR`, `CHAR`, `SMALLINT`, `INTEGER`, `BIGINT`, `DECIMAL`, `FLOAT`, `REAL`, `DOUBLE`, `BOOLEAN`, `DATE`, `TIME`, `TIME WITH TIME ZONE`, `TIMESTAMP`, and `TIMESTAMP WITH LOCAL TIME ZONE`. Casting to `TIME` or `TIME WITH TIME ZONE` is allowed in a condition but not in a select item, because a result column cannot have either type. To use a date or time in an expression, cast an RFC 3339 string literal: a full-date for `DATE`, a full-time for `TIME`, a date-time for `TIMESTAMP`. Other formats are rejected, including relative words such as `today` and infinity values. Only a string literal can be cast this way; a date or time value can be cast to another date or time type without restriction. **How expressions are evaluated** These behaviors are worth knowing before you write an expression: - Dividing two whole numbers truncates the result: `7 / 2` is `3`. - Division by zero is an error, and so is a result that overflows its type. `INTEGER` is 32-bit, `LONG` is 64-bit, and `DOUBLE` is binary64 floating point. Overflow never wraps around and never produces an infinity. - `LIKE` and `ILIKE` treat only `%` and `_` as wildcards. To match one of them literally, add `ESCAPE ''`: the escape character makes the character after it literal, so `'a!_b' ESCAPE '!'` matches `a_b`. The escape must be exactly one character. - `CONCAT` and `||` are not interchangeable. `CONCAT` ignores nulls and returns an empty string when all values are null. With `||`, a single null makes the whole result null. - `CAST` to `FLOAT` produces the same double-precision type as `CAST` to `DOUBLE`. **Not supported.** Anything outside the supported syntax is rejected, in particular `SELECT *`, aggregation and `GROUP BY`, window functions, subqueries, and set operations such as `UNION`. Expressions and joins can nest at most 256 levels deep. **Further restrictions** Three restrictions do not follow from the syntax: - A statement cannot read the same table twice, so a table cannot be joined to itself. - A table with effective dates enabled cannot be read, and an attribute that references such a table cannot be used. The rest of the table that contains that attribute can be read normally. - A reference attribute can only be used after its own table has been read. If `b` has a reference attribute `ref`, `FROM a JOIN b ON b.ref = a.x` is rejected and `FROM b JOIN a ON b.ref = a.x` is accepted. ## Response `200` The statement's result. - SqlStatementResult - `meta` SqlStatementResultMetadata, required - `next` string: Encoded string representing the next page. Omitted if there are no more pages. - `total` integer: Total number of items available across all pages. - `columns` SqlResultColumn[], required: The result columns, in the order the statement selects them. - `name` string, required: The alias from the select item's `AS` clause, or the attribute name when the column has no alias. - `dataType` string, required: @ExtensibleEnum The data type of the values in a result column. - `data` SqlResultRow[], required: The result rows. Their order is defined only when the statement has an `ORDER BY`. Without one, the same statement can return the rows in a different order on another run. - `values` string[], required: One value per result column, in the order of `meta.columns`. Every value is a string, and the column's `dataType` tells you how to read it. A column with no value is `null`, whether the data holds a null or an outer join has no match on that side. | `dataType` | Format | |---|---| | `STRING` | The value itself, unaltered. | | `INTEGER`, `LONG` | Base-ten digits with a leading `-` only when negative: no `+`, no leading zeros, no group separators. | | `DOUBLE` | The ECMA-262 `Number::toString` format: plain digits for magnitudes from 1e-7 up to, but not including, 1e21, exponent notation outside that range, `NaN`, `Infinity`, and `-Infinity` spelled out, and negative zero rendered as `0`. | | `BOOLEAN` | `true` or `false`. | | `DATE` | An RFC 3339 full-date, for example `2026-01-15`. | | `DATETIME` | An RFC 3339 date-time in UTC, for example `2026-01-15T09:30:00Z`. | A `DATETIME` value can have fractional seconds, to a maximum of six decimal places. The number of decimal places carries no meaning (`.5` and `.500` are the same instant), so compare parsed instants rather than the strings. ## Other responses - `400`: Bad request. The request parameters or body are invalid. - `403`: Forbidden. You do not have permission to access the requested resource. --- [API](https://skmtc.dev/ataccama/apis/ataccama-one-agentic-data-trust-platform-public-api.md) - [All operations](https://skmtc.dev/ataccama/apis/ataccama-one-agentic-data-trust-platform-public-api/llms.txt) - [OpenAPI document](https://skmtc.dev/ataccama/apis/ataccama-one-agentic-data-trust-platform-public-api/revisions/46f16e0487f8?raw)