Logical SQL for Cross-Subject Area Joins and Self-Joins
Oracle Transactional Business Intelligence (OTBI) provides powerful ad-hoc reporting capabilities, but sometimes business requirements call for analyses that are not directly supported by the subject area joins configured in the Oracle BI Repository. Whether you want to combine data from unrelated subject areas or perform advanced self-joins, Logical SQL is your best tool.
The Challenge: When Standard OTBI Joins Aren’t Enough
By default, OTBI only allows you to join data from tables that are already linked in the subject area, if no relationship exists, you simply can’t pull those columns together in the visual analysis editor. Likewise, self-joins (joining a table to itself, for advanced hierarchies or comparisons) are not possible in Answers by dragging and dropping.
The Solution: Writing Logical SQL
With Logical SQL, you can define your own dataset logic (think: subqueries, unions, manual joins), even when OTBI’s default subject area model falls short. Logical SQL is accessed via the “Advanced” tab in an OTBI analysis here, you can paste and modify SQL to fit your needs.
Example Use Case 1: Cross-Subject Area Joins
Suppose you want to combine employee assignments with departmental budgets, but these reside in separate subject areas.
Approach:
Create separate analyses for each subject area and obtain the Logical SQL (from Advanced > SQL Issued).
Use Logical SQL to build a query that performs a join (JOIN, LEFT OUTER JOIN) based on a key field (e.g., department ID).
SELECT a."Department Name", a."Employee Name", b."Budget Amount"
FROM
(SELECT ... FROM "Worker Assignment" WHERE ...) a
LEFT JOIN
(SELECT ... FROM "Department Budget" WHERE ...) b
ON a."Department ID" = b."Department ID"
Example Use Case 2: Self-Joins
Suppose you must compare a worker’s current assignment to their previous one. Standard OTBI does not offer a direct way, but Logical SQL lets you join the assignment table to itself.
SELECT a."Worker Name", a."Current Department", b."Previous Department"
FROM
(SELECT ... WHERE "Assignment
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
