Scribd
Upload
16 views5 pages

SQL to MCPS Conversion Guide

The document provides a guide for Oracle Unity users on converting SQL queries to MCPS queries using the Expert Config interface. It outlines the step-by-step process, limitations, supported functions, and error reporting for the conversion. Users must follow specific guidelines, such as using explicit aliases and ensuring case sensitivity, to achieve accurate results.

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
16 views5 pages

SQL to MCPS Conversion Guide

The document provides a guide for Oracle Unity users on converting SQL queries to MCPS queries using the Expert Config interface. It outlines the step-by-step process, limitations, supported functions, and error reporting for the conversion. Users must follow specific guidelines, such as using explicit aliases and ensuring case sensitivity, to achieve accurate results.

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 To MCPS Converter

Table of Contents
SQL TO MCPS CONVERTER ................................................................................................................................ 1
INTRODUCTION ............................................................................................................................................... 2
CONVERT SQL TO MCPS IN EXPERT CONFIG ....................................................................................................... 2
LIMITATIONS & USAGE GUIDELINES ................................................................................................................................... 3
Supported Functions ............................................................................................................................................. 4
ERROR REPORTING & TROUBLESHOOTING .......................................................................................................................... 5
USER ROLES ..................................................................................................................................................... 5
LEARN MORE ................................................................................................................................................... 5

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

Introduction
Unity users can now seamlessly convert SQL queries into MCPS queries using the Expert Config
interface. The generated MCPS can be reused across multiple features, including external
segments, DSVs for intelligent attributes and 360 profile explorer. This feature simplifies usage
by allowing users to work with familiar SQL instead of MCPS queries and accelerates adoption
by making advanced features more accessible to technical teams.

This guide outlines the step-by-step process to convert SQL queries into MCPS queries using
Expert Config.

Convert SQL to MCPS in Expert Config


Follow these steps to convert a SQL query into MCPS via the Expert Config interface in Oracle
Unity:
• Open Expert Config
Navigate to the following URL:
[Link]
• Set Request Type
o Select POST from the HTTP method drop-down
o Select api-data as the target API
• Enter Endpoint
In the /data/ input field, enter:
query/sql2mcps
• Input SQL Query
In the Request text area, paste your SQL query. The SQL-to-MCPS converter supports
only SQL written in the MySQL dialect.
Please ensure your queries follow MySQL syntax to avoid parsing errors or unexpected
results.
• Run the Request
Click Run to initiate the conversion.
• View the Result
The corresponding MCPS query will be displayed in the Response text area.

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

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 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]
3 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

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
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>)
4 Oracle Unity
Copyright © 2025, Oracle and/or its affiliates / Public
Unity

Aggregate Functions
Following aggregate functions are supported
SUM, MAX, AVG, COUNT, MIN, GROUP_CONCAT, STD, STDDEV, STDDEV_POP,
STDDEV_SAMP, VAR_POP, VAR_SAMP, VARIANCE

Error Reporting & Troubleshooting


SQL Convertor Errors
• Example: Query parse exception on line 10, column 4
• Causes:
o Invalid MySQL syntax
o Missing aliases in expressions (e.g. MAX([Link]) without alias)
o Use of unquoted reserved words as
identifiers [Link]
o Invisible whitespace (tab/space) at error position

User Roles
Users with any of the following roles can use the SQL to MCPS endpoint in Expert config

• Instance Admin

Learn More
User Roles

External Segments

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

Common questions

Powered by AI

Case sensitivity requirements for SQL queries influence the MCPS conversion process by mandating consistent use of case in table names, aliases, and column names. If case sensitivity is not adhered to, it can lead to errors in query execution because MCPS differentiates between identifiers based on case. Ensuring case consistency helps prevent such errors and guarantees the successful and correct conversion of queries .

The SQL-to-MCPS converter supports row-level functions such as scalar and date/time functions, window functions, and aggregate functions. Support for these function types is critical for data manipulation as they provide a broad range of capabilities required for processing data, performing calculations, transforming data formats, and generating summary statistics. The robustness of these supported functions enables comprehensive data manipulation and analysis within Oracle Unity .

Using explicit equi-joins instead of implicit joins enhances query clarity and consistency during SQL to MCPS conversion by clearly defining the relationships between tables. This ensures that the intent of the join and the conditions are explicitly stated, reducing the likelihood of ambiguity or misinterpretation by the conversion engine. This practice results in more understandable and maintainable queries that align with MCPS requirements .

Using ANSI_QUOTES for reserved keywords like Date, Order, Primary, and Sequence is important because it avoids potential syntax errors during conversion. Reserved keywords can conflict with SQL's syntax rules, causing parsing errors if not properly delineated with quotes. This practice ensures the queries are parsed correctly and the conversion process remains efficient and error-free .

The Expert Config interface in Oracle Unity facilitates the SQL to MCPS conversion process by providing a web-based platform where users can input their SQL queries. The interface guides users through specifying request types, entering endpoints, and executing the conversion, displaying the results as MCPS queries. This simplifies the conversion process and allows users to utilize advanced features without needing to understand MCPS syntax in detail .

When converting SQL to MCPS using Oracle Unity's Expert Config, the main limitations include the requirement for transitive joins only, the use of explicit aliases to avoid ambiguity, the use of explicit equi-joins instead of implicit joins, avoidance of single letter aliases, case sensitivity for table names, aliases, and column names, and the use of ANSI_QUOTES for reserved keywords. These limitations are important as they ensure accurate and consistent conversion by avoiding ambiguous SQL statements and ensuring compatibility with the MCPS query syntax. Additionally, adhering to these guidelines prevents parsing errors and improves query readability and maintainability .

Oracle Unity supports several window functions such as ROW_NUMBER, DENSE_RANK, FIRST_VALUE, LAST_VALUE, LEAD, LAG, and SUM, which can be used in SQL queries to perform calculations across sets of rows related to the current row. These functions are significant in data analysis as they allow for complex operations like ranking or finding cumulative sums without requiring self-joins or additional subqueries. This enhances the efficiency and performance of data analytics tasks .

To access the SQL to MCPS endpoint in Oracle Unity's Expert Config, a user must have the 'Instance Admin' role. This role is aligned with organizational responsibilities because it ensures that only users with appropriate administrative privileges, typically responsible for overseeing data configurations and ensuring system integrity, can perform such operations. This control helps maintain security and the accuracy of data transformations within the organization .

SQL queries may fail to convert properly in Oracle Unity due to invalid MySQL syntax, missing aliases in expressions (e.g., using MAX without an alias), use of unquoted reserved words as identifiers, or the presence of invisible whitespace at error positions. These errors typically cause query parse exceptions, underscoring the importance of precise syntax and adherence to conversion guidelines, such as using explicit aliases and ensuring case sensitivity is maintained .

Oracle Unity's SQL to MCPS converter enhances usability for technical teams by allowing them to work with familiar SQL syntax, reducing the need to learn MCPS specifics. This enhancement is facilitated by features like the capability to seamlessly convert queries using the Expert Config interface, which allows SQL queries to be inputted and automatically transformed into reusable MCPS queries. The platform also supports integration across various features such as external segments and intelligent attributes, which accelerates the adoption of advanced analytics features .

You might also like