Scribd
Upload
22 views6 pages

Creating SQL Segments in Oracle Unity

Oracle Unity allows users to create and manage advanced segments using SQL in the Segment Builder, enhancing flexibility and efficiency for complex segmentation logic. This guide provides detailed steps for creating SQL segments, including when to use them, limitations, and supported functions. Users can switch from a visual canvas to SQL mode, enabling advanced operations and custom joins not possible through the visual interface.

Uploaded by

zelanger
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views6 pages

Creating SQL Segments in Oracle Unity

Oracle Unity allows users to create and manage advanced segments using SQL in the Segment Builder, enhancing flexibility and efficiency for complex segmentation logic. This guide provides detailed steps for creating SQL segments, including when to use them, limitations, and supported functions. Users can switch from a visual canvas to SQL mode, enabling advanced operations and custom joins not possible through the visual interface.

Uploaded by

zelanger
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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

Common questions

Powered by AI

Oracle Unity facilitates learning and troubleshooting through resources such as detailed documentation for creating SQL segments, error reporting via dialog boxes, and incorporating features like syntax highlighting and automatic SQL querying aids. Support for user roles also ensures that those with appropriate permissions can access and utilize these features efficiently, thus streamlining the learning curve and minimizing the chances of user-induced errors .

The SQL editor in Oracle Unity provides error detection capabilities by highlighting syntax errors and unsupported SQL structures when users click 'Count' or 'Save'. Error messages inform users of issues like invalid MySQL syntax, missing aliases, usage of unquoted reserved words, and invisible whitespace. These features allow users to quickly identify and resolve errors, ensuring the accuracy of their SQL queries .

Oracle Unity supports a wide range of SQL functions including scalar functions (e.g., ABS, CONCAT, CURRENT_DATE), date/time functions (e.g., DATEDIFF, DATE_ADD), window functions (e.g., ROW_NUMBER, DENSE_RANK), and aggregate functions (e.g., SUM, AVG, COUNT). These functions allow for complex data manipulation tasks like calculating aggregates, performing date calculations, and window-specific operations, enabling more advanced segmentation and analytics .

To convert a visual canvas segment to an SQL segment in Oracle Unity, first select the segment from the Segments interface. Initiate the switch to SQL mode by confirming the prompt indicating the inability to revert to the visual form builder. The platform autogenerates a SQL version of the visual canvas-defined segmentation criteria, which can then be manually edited within the SQL editor to define additional or advanced logic .

Best practices for accurate SQL segment conversions in Oracle Unity include using explicit aliases to prevent ambiguity and employing explicit equi-joins as opposed to implicit joins. It is also essential to maintain case sensitivity with table and column names, and use double quotes for reserved keywords. Adhering to these practices ensures correct, consistent segment conversions and enhances query readability and maintainability .

The SQL segment feature in Oracle Unity is advantageous when complex segmentation logic is required, such as scenarios necessitating custom joins between non-predefined relational tables, or when using advanced aggregate functions on non-numeric or derived fields. It is also preferable for transformative operations like conditional logic and string truncation that are unsupported through the visual canvas .

In Oracle Unity, SQL segments only support transitive joins (standard join chains) and explicit equi-joins. Implicit joins using comma-separated tables are not allowed. Explicit aliases should be used to avoid ambiguity, and aliases should be meaningful for clarity and compatibility. Case sensitivity must also be maintained for table and column names .

Users can manage existing SQL segments within Oracle Unity by accessing the Segments landing page, where the segments are listed. Modifications can be made by clicking on the SQL segment item and using the SQL editor to edit the query. This interface also supports reusing segments for delivery and export jobs, thus facilitating ongoing updates and optimization of segmentation strategies .

The SQL segmentation feature enhances operational efficiency and reduces time-to-market by allowing technical users to create and manage advanced segments directly within Oracle Unity. This eliminates the need for external segment configurations, providing more flexibility, streamlining workflows, and enabling quicker launch of campaigns .

The SQL editor in Oracle Unity enhances segment logic crafting through features like syntax highlighting, error detection, and smart prompts for listing tables and functions. It also offers a beautify button for improved readability of queries and allows previewing sample records to validate query results. Additionally, MySQL syntax support ensures compatibility with the underlying database structure .

You might also like