Skip to content
Bitta Apps

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.

Next step

Ready to try Bitta Code Studio?