Unity

SQL Segments

Table of Contents
SQL SEGMENTS ................................................................................................................................................ 1
INTRODUCTION ............................................................................................................................................... 2
WHEN TO USE SQL SEGMENT ........................................................................................................................... 2
HOW TO CREATE A SQL SEGMENT..................................................................................................................... 2
LIMITATIONS & USAGE GUIDELINES ................................................................................................................................... 4
Supported Functions ............................................................................................................................................. 5
ERROR REPORTING & TROUBLESHOOTING .......................................................................................................................... 6
USER ROLES ..................................................................................................................................................... 6
LEARN MORE ................................................................................................................................................... 6

1 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

Introduction
Unity now enables technical users to create and manage advanced segments using SQL within
the Segment Builder. Users can easily switch from the visual canvas to SQL mode when they
need to define complex segmentation logic, providing a smooth and connected experience for
both simple and advanced use cases. This capability eliminates reliance on external segments
configured via expert config, enhances flexibility, improves operational efficiency and
significantly reduces time-to-market for launching campaigns.

This guide outlines the step-by-step process to create SQL segments.

When to Use SQL Segment

Use this feature when:
• You need complex segmentation logic that cannot be built using the visual canvas.
• Scenarios that require custom joins between tables especially when those tables do not
have pre-defined relationships are better handled through SQL.
• Advanced aggregate functions (e.g., SUM, COUNT, COUNT DISTINCT) on non-
numeric or derived fields such as unique counts of phone numbers or email domains are
more flexible in SQL.
• Transformative operations like conditional logic, string truncation and other row-level
transformations that are not supported through the visual canvas.

How to Create a SQL Segment

Follow these steps to create an SQL segment in Oracle Unity:

1. Click the Oracle icon in the bottom-right corner to open the navigation menu.
2. Select Segments.
3. In the top-right corner, click Create to open the Create new segment dialog.
4. Enter a unique name for your segment.
5. Add a brief description.
6. Choose the base object you want to build the segment on.
7. Click Continue.
8. Begin building the segment using the visual canvas (refer to the Creating
Segments documentation for guidance).
9. Click Switch to SQL mode when you want to define advanced segmentation logic using
SQL.

2 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

10. Review the message "This option is for advanced users to build segments using SQL
commands. Once you switch to SQL mode, you will not be able to return to the form
based segment builder. Are you sure you want to continue?"
11. Click the Switch to SQL button to confirm.
12. View the auto-converted SQL version of any previously defined segmentation criteria
defined in the visual canvas including global exclusion and reference segments. Please
note that any changes made to global exclusion and reference segments after switching to
SQL mode will not be reflected automatically in SQL query. You will need to manually
update the SQL query to include those changes.

3 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

13. Use the SQL editor to craft your segment logic either by editing the auto-generated query
or by writing a new SQL query
14. Ensure it follows MySQL syntax, as the editor only supports MySQL dialect.
15. Click the Beautify button to format your SQL query for readability.
16. Use features like smart prompts (press # for list of tables, press @ for list of
functions), syntax highlighting and error detection to help build your query.
17. Click Count to validate and execute the SQL query.
18. Preview up to 100 sample records by clicking the Sample tab. Note: There is no separate
tab for personalized attributes, they are included directly in your SELECT query
(e.g., SELECT first_name, email FROM customer).
19. Save your segment by clicking the Save button. It will now appear on
the Segments landing page as a fully clickable and editable item.
20. Use the SQL segment in segment delivery / Export jobs to export segments to a
marketing orchestration platform like Oracle Responsys or SFTP
21. Modify the SQL Segment at any time by clicking on the SQL segment
in Segments listing page.

Limitations & Usage Guidelines

To ensure accurate and consistent conversion, the following limitations and best practices apply
1. Only transitive joins are supported
Only standard join chains (transitive joins) are supported in SQL conversion
2. Use explicit Aliases to avoid ambiguity
Avoid potentially ambiguous statements by always using table aliases.
• ✅ OK:
SELECT [Link] FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON
[Link] = [Link]
• ❌ Not OK:
SELECT ID FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON
[Link] = [Link]
3. Only explicit equi-joins are supported
Implicit joins using comma-separated tables are not allowed.
• ✅ OK:
SELECT [Link] FROM mcpsdw_Customer cust JOIN mcpsdw_Event event ON
[Link] = [Link]
• ❌ Not OK:
SELECT [Link] FROM mcpsdw_Customer cust, mcpsdw_Event event WHERE
[Link] = [Link]
4. Avoid single letter aliases
Use meaningful aliases for clarity and compatibility.
• ✅ OK:
SELECT [Link] FROM mcpsdw_Customer cust
• ❌ Not OK:
SELECT [Link] FROM mcpsdw_Customer c
4 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

5. Case sensitivity applies

Table names, aliases and column names are case sensitive. Ensure consistent usage.
6. Use ANSI_QUOTES for reserved keywords like Date, Order, Primary, Sequence etc.
When referring to reserved keywords, wrap them in double quotes.
• ✅ OK:
SELECT ev."Date" FROM mcpsdw_Event ev
• ❌ Not OK:
SELECT [Link] FROM mcpsdw_Event ev

Supported Functions

Row-Level Functions
Includes most common scalar and date/time functions
ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, CEILING, CONCAT, COS, CURRENT_DATE,
CURRENT_TIMESTAMP, DATEDIFF, DATE_ADD, DATE_SUB, DAY, EXP, FLOOR,
HOUR, IFNULL, INSTR, LEFT, LENGTH, LN, LOG, LOWER, LPAD, LTRIM, MINUTE,
MOD, MONTH, POWER, REPLACE, REVERSE, RIGHT, ROUND, RPAD, RTRIM, SIGN,
SIN, SQRT, SUBSTRING, TAN, TIMESTAMP, TIMESTAMPADD, TIMESTAMPDIFF,
TRIM, TRUNCATE, UPPER, YEAR

Window Functions
Following window functions are supported

• ROW_NUMBER(<val>) OVER (PARTITION BY <val> [ORDER BY <val>

ASC|DESC]*)
• DENSE_RANK(<val>) OVER (PARTITION BY <val> [ORDER BY <val>
ASC|DESC]*)
• FIRST_VALUE(<val>) OVER (PARTITION BY <val> [ORDER BY <val>
ASC|DESC]*)
• LAST_VALUE(<val>) OVER (PARTITION BY <val> [ORDER BY <val>
ASC|DESC]*)
• LEAD(<val> [, offset, default]) OVER (PARTITION BY <val> [ORDER BY <val>
ASC|DESC]*)
• LAG(<val> [, offset, default]) OVER (PARTITION BY <val> [ORDER BY <val>
ASC|DESC]*)
• SUM(<val>) OVER (PARTITION BY <val>)

Aggregate Functions
Following aggregate functions are supported

5 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

SUM, MAX, AVG, COUNT, MIN, GROUP_CONCAT, STD, STDDEV, STDDEV_POP,

STDDEV_SAMP, VAR_POP, VAR_SAMP, VARIANCE

Error Reporting & Troubleshooting

SQL Convertor Errors
When you click Count or Save, any SQL related errors are shown in dialog boxes so you can
quickly identify and fix them.

Example: Query parse exception on line 10, column 4

Causes:
• Invalid MySQL syntax
• Missing aliases in expressions (e.g. MAX([Link]) without alias)
• Use of unquoted reserved words as
identifiers [Link]
• Invisible whitespace (tab/space) at error position

User Roles
Users with any of the following roles can use the SQL segments feature

• Instance Admin

Learn More
User Roles

External Segments

Segment Deliveries

6 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public