Aggregates vs SQL Queries: When to Use Each in OutSystems

Use Advanced SQL when your query requires ANY of these: 1. SUBQUERIES: `SELECT * FROM Employee WHERE DepartmentId IN (SELECT Id FROM Department WHERE Budget > 100000)` 2. UNION / INTERSECT / EXCEPT: combining results from two separate queries 3. Bulk UPDATE or DELETE: `UPDATE Employee SET IsActive = 0 WHERE DepartmentId = @DeptId` — updating 10,000 rows at once instead of looping through them 4. Window functions (SQL Server: ROW_NUMBER() OVER, RANK() OVER, LEAD/LAG) — not available in Aggregates 5. HAVING clause: filter by aggregate results (e.g., `HAVING COUNT(*) > 5`) — Aggregate Filters tab maps to WHERE, not HAVING If your query does NOT require these, use an Aggregate. Complex multi-join queries with many filters can still be expressed as Aggregates.

Advanced SQL is only available inside Server Actions — NOT in Client Actions, Screen Aggregates, or Preparation actions. In Service Studio: Logic tab → Server Actions → right-click → Add Server Action. Name it 'GetHighBudgetDeptEmployees'. In the action flow editor, drag an SQL element from the Toolbox (under Data section) into the flow. The SQL editor opens. The SQL editor has: - Query text area: write your SQL - Parameters tab: define @ParameterName inputs - Output Structure tab: define the return record structure - Test tab: test with literal values

In the SQL editor, write your query using OutSystems' entity reference syntax: `{EntityName}` — refers to the entity's database table `{EntityName}.[AttributeName]` — refers to a specific column `@ParameterName` — binds an input parameter Example — find employees in departments with budget over a threshold: ```sql SELECT {Employee}.[Id], {Employee}.[FirstName], {Employee}.[LastName] FROM {Employee} WHERE {Employee}.[DepartmentId] IN ( SELECT {Department}.[Id] FROM {Department} WHERE {Department}.[Budget] > @MinBudget ) ORDER BY {Employee}.[LastName] ``` The curly brace syntax is mandatory — OutSystems translates `{Employee}` to the actual physical table name at compile time, ensuring your query still works after table renames.

1/* Advanced SQL Query — Subquery example */
2SELECT
3  {Employee}.[Id],
4  {Employee}.[FirstName],
5  {Employee}.[LastName],
6  {Employee}.[Email],
7  {Department}.[Name] AS DepartmentName
8FROM {Employee}
9INNER JOIN {Department} ON {Employee}.[DepartmentId] = {Department}.[Id]
10WHERE {Employee}.[DepartmentId] IN (
11    SELECT {Department}.[Id]
12    FROM {Department}
13    WHERE {Department}.[Budget] > @MinBudget
14    AND {Department}.[IsActive] = 1
15)
16ORDER BY {Department}.[Name], {Employee}.[LastName]

In the SQL Parameters tab: - Click Add → Name: MinBudget, Data Type: Decimal, Expand Inline: No - 'Expand Inline' should be No for scalar values (prevents SQL injection). Only use Yes for dynamic column names or ORDER BY clauses — with caution. In the Output Structure tab: - Click Add Record → select an existing Structure, or click Add Attribute to define the output columns manually - Match the column names and types to what your SELECT returns: - Id: Long Integer - FirstName: Text - LastName: Text - Email: Email - DepartmentName: Text In the action flow, connect the SQL node's Output to a local variable: - Add a local variable of type `[YourSQL].List` or create a Structure matching your output - The SQL node returns a RecordList of your output structure

1/* SQL Parameters tab */
2Name: MinBudget    Type: Decimal    Expand Inline: No
3
4/* Output Structure (matches SELECT columns) */
5Attribute: Id             Type: Long Integer
6Attribute: FirstName      Type: Text
7Attribute: LastName        Type: Text
8Attribute: Email           Type: Email
9Attribute: DepartmentName  Type: Text
10
11/* Action flow: accessing SQL output */
12GetHighBudgetDeptEmployees.List           /* RecordList */
13GetHighBudgetDeptEmployees.List.Current.Id
14GetHighBudgetDeptEmployees.List.Current.DepartmentName
15GetHighBudgetDeptEmployees.Count          /* total rows */
16GetHighBudgetDeptEmployees.List.Empty     /* Boolean */

Aggregates fetch data; they cannot update or delete data. For bulk operations — deactivating all employees in a department, updating prices by percentage — Advanced SQL is the only performant option. In a Server Action, add an SQL node. Write a bulk UPDATE: ```sql UPDATE {Employee} SET {Employee}.[IsActive] = 0 WHERE {Employee}.[DepartmentId] = @DepartmentId ``` For bulk UPDATE, the Output Structure should be left empty (UPDATE does not return rows). The SQL node's output is just a Count of affected rows. IMPORTANT: OutSystems wraps Server Actions in transactions by default. If this SQL node is in a Server Action that raises an exception, the UPDATE is rolled back automatically. NEVER write `UPDATE {Employee} SET IsActive = 0` without a WHERE clause in production — this would deactivate all employees.

1/* Bulk UPDATE — deactivate all employees in a department */
2UPDATE {Employee}
3SET    {Employee}.[IsActive] = 0,
4       {Employee}.[UpdatedOn] = GETDATE()    /* O11: SQL Server */
5/* ODC PostgreSQL equivalent: */
6/* {Employee}.[UpdatedOn] = NOW() */
7WHERE  {Employee}.[DepartmentId] = @DepartmentId
8
9/* Parameters tab */
10Name: DepartmentId    Type: Department Identifier    Expand Inline: No
11
12/* Bulk DELETE (archive old records) */
13DELETE FROM {AuditLog}
14WHERE {AuditLog}.[CreatedOn] < @CutoffDate
15
16/* Bulk INSERT from SELECT (copy to archive) */
17INSERT INTO {EmployeeArchive} ({EmployeeArchive}.[FirstName], {EmployeeArchive}.[Email])
18SELECT {Employee}.[FirstName], {Employee}.[Email]
19FROM {Employee}
20WHERE {Employee}.[HireDate] < @CutoffDate

O11 uses SQL Server (T-SQL). ODC uses Aurora PostgreSQL. Key syntax differences you must handle when writing Advanced SQL: | Operation | O11 (SQL Server) | ODC (PostgreSQL) | |---|---|---| | Current timestamp | GETDATE() | NOW() | | Limit rows | TOP 10 / SELECT TOP (@n) | LIMIT 10 / LIMIT @n | | String concat | + operator | || operator or CONCAT() | | Cast to text | CAST(n AS NVARCHAR) | CAST(n AS TEXT) | | Boolean true | 1 | TRUE | | ISNULL | ISNULL(col, default) | COALESCE(col, default) | | Window functions | ROW_NUMBER() OVER (ORDER BY ...) | ROW_NUMBER() OVER (ORDER BY ...) — same | Best practice: avoid Advanced SQL altogether for cross-platform modules. When SQL is necessary, write two versions and use a Site Property (O11) or Setting (ODC) to switch between them — or use Aggregates wherever possible.

1/* O11 (SQL Server T-SQL) */
2SELECT TOP (@MaxRows)
3  {Employee}.[Id],
4  {Employee}.[FirstName] + ' ' + {Employee}.[LastName] AS FullName,
5  ISNULL({Department}.[Name], 'No Department')         AS DepartmentName,
6  ROW_NUMBER() OVER (ORDER BY {Employee}.[LastName])   AS RowNum
7FROM {Employee}
8LEFT JOIN {Department} ON {Employee}.[DepartmentId] = {Department}.[Id]
9WHERE {Employee}.[IsActive] = 1
10ORDER BY {Employee}.[LastName]
11
12/* ODC (PostgreSQL) */
13SELECT
14  {Employee}.[Id],
15  {Employee}.[FirstName] || ' ' || {Employee}.[LastName] AS FullName,
16  COALESCE({Department}.[Name], 'No Department')         AS DepartmentName,
17  ROW_NUMBER() OVER (ORDER BY {Employee}.[LastName])     AS RowNum
18FROM {Employee}
19LEFT JOIN {Department} ON {Employee}.[DepartmentId] = {Department}.[Id]
20WHERE {Employee}.[IsActive] = TRUE
21ORDER BY {Employee}.[LastName]
22LIMIT @MaxRows