OutSystems SQL query authoring for both O11 (MSSQL) and ODC (Aurora PostgreSQL / ANSI-92). Covers entity references, aggregate syntax, advanced SQL, joins, functions, parameterized queries, and platform-specific syntax differences. Also handles converting ODC data models into Flowmo-compatible PostgreSQL schemas. Use when the developer is writing SQL queries, creating aggregates, working with data retrieval logic, or needs to mirror an ODC schema locally.
Before writing any SQL, determine the target platform:
{Entity}.[Attribute] notation (same as O11) but ODC has two sub-modes that ODC Studio selects automatically:
LIMIT, ||, RANDOM(), NOW(), etc. LIKE on text columns requires caseaccent_normalize().{Entity}.[Attr] notation is recommended.Ask the developer which platform they are targeting if not already clear. Despite the shared notation, function and clause differences cause runtime errors if O11 and ODC syntax is mixed.
SELECT {Entity}.[Attribute1], {Entity}.[Attribute2]
FROM {Entity}
WHERE {Entity}.[IsActive] = 1
{EntityName} in curly braces[AttributeName] in square brackets1 / 0 (stored as integer)SELECT {Entity}.[Attribute1], {Entity}.[Attribute2]
FROM {Entity}
WHERE {Entity}.[IsActive] = 1
{EntityName} in curly braces — same notation as O11[AttributeName] in square brackets — same notation as O111 / 0 (stored as integer, same as O11)SELECT * is not valid — always qualify as SELECT {Entity}.*NOW(), LIMIT, ||, RANDOM(), etc.{Entity}.[Attr] is recommendedBoth O11 and ODC SQL nodes use {Entity}.[Attribute] notation. The differences are in functions, operators, and clause syntax.
| Operation | O11 (MSSQL) | ODC (ANSI-92 / Aurora PostgreSQL) |
|---|---|---|
| Entity/attribute notation | {Entity}.[Attribute] | {Entity}.[Attribute] — same |
| Boolean values | 1 / 0 (integer) | 1 / 0 (integer — same) |
| Select all columns | SELECT * FROM {Entity} | SELECT {Entity}.* FROM {Entity} — must qualify |
| Current date/time | GETDATE() | NOW() |
| Top N rows | SELECT TOP N * FROM {Entity} | SELECT {Entity}.* FROM {Entity} LIMIT N |
| Random rows | ORDER BY NEWID() | ORDER BY RANDOM() |
| Null coalesce | ISNULL(x, default) | COALESCE(x, default) |
| String concat | 'a' + 'b' | 'a' || 'b' |
| Date diff | DATEDIFF(day, d1, d2) | d2 - d1 or EXTRACT(...) |
| Substring | SUBSTRING(s, start, len) | SUBSTRING(s FROM start FOR len) |
| Type cast | CAST(x AS INT) | CAST(x AS INTEGER) or x::integer |
| IF/ELSE in query | IIF(cond, t, f) | CASE WHEN cond THEN t ELSE f END |
| String length | LEN(s) | LENGTH(s) |
| Trim | LTRIM(RTRIM(s)) | TRIM(s) |
| LIKE (case-insensitive) | {Entity}.[Name] LIKE '%val%' | caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%val%') — bare LIKE fails on text columns |
| Pagination | OFFSET N ROWS FETCH NEXT M ROWS ONLY | LIMIT M OFFSET N |
| INSERT column list | INSERT INTO {Entity} ({Entity}.[Attr1]) | Internal: INSERT INTO {Entity} ([Attr1]) — no prefix. External (ANSI-92): INSERT INTO {Entity} ({Entity}.[Attr1]) — qualify with entity prefix (recommended) |
| UPDATE SET clause | UPDATE {Entity} SET {Entity}.[Attr] = val | UPDATE {Entity} SET [Attr] = val — no entity prefix in SET |
| UPSERT | MERGE INTO ... USING ... | UPSERT INTO {Entity} ({Entity}.[Id], {Entity}.[Attr]) VALUES (@Id, @Value) — requires non-generated PK; never returns a result |
Read references/query-patterns.md whenever you need example queries — CRUD (create, read, update, delete), aggregation, pagination, subqueries, and CTEs for both O11 and ODC.
Always use parameters (@ParamName) for input values — NEVER concatenate user input into SQL strings. OutSystems enforces this in Advanced SQL but ensure it in any generated queries.
-- CORRECT (O11)
WHERE {Entity}.[Name] LIKE '%' + @SearchTerm + '%'
-- CORRECT (ODC) — LIKE on text columns requires caseaccent_normalize
WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%' || @SearchTerm || '%')
-- WRONG (SQL injection risk)
WHERE {Entity}.[Name] LIKE '%' + 'user input here' + '%'
Prefer Aggregates (visual query builder) for simple queries:
Use Advanced SQL when you need:
After writing a SQL query, verify:
@ParamName parameters — no string concatenation of user inputMax Records is explicitly set on the Advanced SQL node (never leave it unlimited){Entity}.[Attribute] notation for both O11 and ODC — ODC uses ANSI-92 syntax, not raw PostgreSQL in SQL nodesOrder, User, Group, Table) — {Entity} notation handles escaping automatically in ODC SQL nodes; no manual quoting neededIf any check fails, fix the query before presenting the output.
OutSystems Nulls: In O11, NullTextIdentifier(), NullDate(), etc. map to NULL in SQL. In ODC, use standard NULL comparisons.
Max Records: Always set Max Records on Advanced SQL queries. Default is unlimited — this can cause performance issues.
Output Structure: Advanced SQL queries must have an Output Structure defined. The column names in SELECT must match the Output Structure attributes.
Test Queries: O11 allows testing SQL in Service Studio. ODC requires deployment to test. Always validate syntax for the target platform.
Reserved Words and {Entity} notation: {Order}, {User}, {Group} are automatically escaped by the ODC SQL node runtime — no manual quoting. However, the Flowmo .advance.sql parser translates these to raw PostgreSQL for local testing, where reserved words must be quoted. The parser handles {User} → "user" automatically. If you write raw SQL (non-.advance.sql), you must quote manually:
-- .advance.sql (parser handles it automatically)
SELECT {User}.[Id], {User}.[Name], {User}.[Email]
FROM {User}
WHERE {User}.[Id] = @UserId
-- Parsed to: SELECT "user".id, "user".name, "user".email FROM "user" WHERE "user".id = $1
-- Raw SQL — must quote manually
SELECT u.id, u.name, u.email
FROM "user" u
WHERE u.id = $1
OutSystems User entity fields:
| OutSystems Attribute | Type | Local column |
|---|---|---|
Id | Text (GUID) | id TEXT |
Name | Text | name TEXT |
Email | Text | email TEXT |
PhotoUrl | Text | photo_url TEXT |
Username | Text | username TEXT |
In Flowmo, if your project has a dedicated user table, create it directly as "user":
-- Simple standalone user table
CREATE TABLE "user" (
id TEXT PRIMARY KEY, -- OutSystems GUID (e.g. 'user-001')
name TEXT NOT NULL,
email TEXT NOT NULL,
photo_url TEXT,
username TEXT NOT NULL,
is_active INTEGER NOT NULL DEFAULT 1
);
SELECT * is invalid in ODC SQL nodes — always qualify as SELECT {Entity}.*. Bare SELECT * causes a runtime error.
LIKE in ODC requires caseaccent_normalize() — ODC uses non-deterministic collations for all text columns. Bare LIKE pattern matching on text columns fails at runtime with a collation error. Always wrap both sides:
WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%' || @SearchTerm || '%')
The collate "default" part is only needed when applying the pattern to a column (non-deterministic collation). You can omit it for literal-only patterns:
WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%something%')
Date Comparisons: O11 uses BETWEEN or DATEDIFF. ODC prefers range comparisons with >= and < or EXTRACT().
Aggregate Functions in WHERE: Use HAVING for aggregate conditions — WHERE runs before GROUP BY.
Index Awareness: Filter on indexed attributes when possible. In O11 check Query Analyzer; in ODC check the database logs.
Read references/odc-schema.md when generating a local Flowmo PostgreSQL schema (database/schema.sql) from an ODC entity model. It covers the type mapping table, conversion rules, a full example, and the db:setup workflow.
Read references/flowmo-cli.md when running or testing queries locally with the Flowmo CLI. It covers db:setup/db:seed/db:reset, db:query usage, flags, parameter types, and a workflow checklist.