Bitta Code Studio reference
SQL syntax
The SQL the SQL Workbench understands: SELECT with joins, grouping, subqueries, UNION, TOP and paging, plus INSERT, UPDATE and DELETE, and how to quote names.
updated: applies-to: 1.0.0.0
The SQL Workbench reads and writes Business Central tables. Table and field names are the Business Central names.
Names
- Put names with spaces or dots in square brackets: [Sales Header], [No.], [Balance (LCY)].
- Strings use single quotes: 'Atlanta'.
- Comments start with two hyphens and run to the end of the line.
SELECT
Supported: SELECT with WHERE, LIKE, IN, BETWEEN, IS NULL, joins (INNER, LEFT, RIGHT, FULL and CROSS), GROUP BY with HAVING, COUNT, SUM, AVG, MIN and MAX, DISTINCT, CASE, subqueries, EXISTS, UNION, TOP, ORDER BY, and OFFSET and FETCH paging.
SELECT TOP 10 [No.], Name, [Balance (LCY)]
FROM Customer
ORDER BY [Balance (LCY)] DESC
INSERT, UPDATE and DELETE
Allowed for users with write access in Studio Users. Writes go through the Security Guard and the Studio Audit Log, and protected tables need GodMode and a prompt. Choose Simulate to see how many rows would be affected without saving anything.
Limits
Results are capped at SQL Max Rows (Studio Setup, Code Studio section). The grid tells you when a result was capped. Use TOP, OFFSET and FETCH, or a WHERE filter, to see the rest, or Export to Excel for the full result.
Samples
Choose Samples in the results pane for ready statements, such as Customers and Top 10 customers by balance.