OBIEE Logical SQL Join Syntax: Why LEFT JOIN Breaks and the Correct Patterns

Why Standard LEFT JOIN Fails in OBIEE

If you've come from standard SQL and tried to write a join in OBIEE Logical SQL, you've probably hit this immediately:

-- This is valid SQL — but it WILL NOT work in OBIEE Logical SQL
SELECT
  w."Worker Name",
  a."Grade Name"
FROM
  "Workforce Management"."Workers" w
  LEFT JOIN "Workforce Management"."Assignments" a
    ON w."Person Number" = a."Person Number"

The BI Server rejects this with an error similar to:

This is not a bug. OBIEE Logical SQL is a semantic query language, not standard SQL. It was designed to query the Oracle BI metadata layer (the RPD / semantic model), not a physical database. The join conditions are defined in the RPD — not in your query. When you write Logical SQL, you are telling the BI Server what you want, and it decides how to join.

How the BI Server Resolves Joins

Understanding this is the key to writing correct Logical SQL. The Oracle BI Server architecture has three layers:

When you write a Logical SQL query, the BI Server:

1. Reads your column references (presentation layer)
2. Maps them to logical table columns (BMM layer)
3. Finds the join path between those logical tables
4. Generates the physical SQL with the correct JOIN ... ON clauses
5. Executes against the database and returns results

You never see step 4. The join happens automatically. This is why LEFT JOIN ... ON in your Logical SQL is rejected — you're trying to define the join, but that's the BI Server's job.

The Correct Implicit Join Pattern

In OBIEE Logical SQL, you join tables by listing them in the FROM clause separated by commas. That's it. The BI Server applies the join path from the RPD.

SELECT
  w."Worker Name",
  a."Grade Name",
  a."Assignment Status"
FROM
  "Workers" w
  LEFT JOIN "Assignments" a
    ON w."Person Number"
       = a."Person Number"

SELECT
  "Worker"."Worker Name",
  "Grade"."Grade Name",
  "Assignment"."Assignment Status"
FROM
  "Workforce Management"
ORDER BY
  "Worker"."Worker Name"

Notice: in Logical SQL you reference the subject area in the FROM clause (e.g., "Workforce Management"), not individual tables. The column references use the folder and column name path. The BI Server resolves the physical join between Worker, Grade, and Assignment through the RPD.

Simulating Outer Joins in Logical SQL

This is the question that bites every Oracle HCM consultant: "I need all workers, even those without a grade or position assigned — how do I LEFT JOIN in OBIEE?"

The short answer: you don't control the join type in Logical SQL. Whether the join is inner or outer is determined by the BMM layer join definition. But there are three practical approaches:

Approach 1: Check the RPD Join Type (Best Fix)

If you need outer join behavior, the join in the Business Model layer should be defined as an outer join. In OBIEE Administration Tool (or OAC Semantic Modeler):

• Open the join between the fact table and the optional dimension
• Set cardinality to 1..N on the fact side, 0..1 on the dimension side
• The BI Server generates a LEFT OUTER JOIN in the physical SQL automatically

This is the correct architectural fix. If you can't modify the RPD, use the workarounds below.

Approach 2: FILTER() for Conditional Aggregation

For measure columns, the FILTER() function is the Logical SQL equivalent of a conditional aggregate. It handles cases where you want to count or sum only when a dimension value exists:

SELECT
  "Worker"."Department Name",
  COUNT("Worker"."Worker ID") AS "Total Workers",
  FILTER(COUNT("Worker"."Worker ID")
         USING "Grade"."Grade Name" IS NOT NULL)
    AS "Workers with Grade",
  FILTER(COUNT("Worker"."Worker ID")
         USING "Grade"."Grade Name" IS NULL)
    AS "Workers Without Grade"
FROM "Workforce Management"

Approach 3: BI Publisher Two-Dataset Merge

When you need a true outer join across subject areas or with data that doesn't join cleanly in the RPD, use BI Publisher with two separate OTBI queries as datasets. Join them in the data model on PERSON_NUMBER or ASSIGNMENT_ID. This gives you full SQL join control in the BIP layer.

The FILTER() Function Pattern in Depth

The FILTER() function is the most powerful Logical SQL tool that most OTBI users miss. It wraps an aggregate function with a USING condition:

FILTER(aggregate_expression USING filter_condition)

The USING clause is a Logical SQL filter expression — same syntax as a WHERE clause predicate but applied to a single measure. This lets you compute multiple conditional counts in a single query pass without subqueries:

SELECT
  "Worker"."Business Unit Name",
  COUNT("Worker"."Worker ID") AS "Total HC",
  FILTER(COUNT("Worker"."Worker ID")
         USING "Worker"."Employment Category" = 'FULL_TIME')
    AS "FT Headcount",
  FILTER(COUNT("Worker"."Worker ID")
         USING "Worker"."Employment Category" = 'PART_TIME')
    AS "PT Headcount",
  FILTER(COUNT("Worker"."Worker ID")
         USING "Worker"."Assignment Status Type" IN
               ('ACTIVE_PROCESS', 'ACTIVE_NO_PROCESS'))
    AS "Active HC"
FROM "Workforce Management"
ORDER BY "Worker"."Business Unit Name"

5 Oracle HCM Join Examples in Logical SQL

Example 1: Workers and Their Primary Assignment Grade

SELECT
  "Worker"."Person Number",
  "Worker"."Worker Name",
  "Assignment"."Assignment Number",
  "Grade"."Grade Name",
  "Job"."Job Name"
FROM "Workforce Management"
WHERE
  "Assignment"."Primary Assignment Flag" = 'Y'
  AND "Assignment"."Assignment Status Type" = 'ACTIVE_PROCESS'
ORDER BY
  "Worker"."Worker Name"

The join between Worker, Assignment, Grade, and Job is resolved by the RPD. You never write it.

Example 2: Department Headcount with Absence Count

SELECT
  "Worker"."Department Name",
  COUNT(DISTINCT "Worker"."Person Number") AS "Headcount",
  FILTER(
    COUNT(DISTINCT "Worker"."Person Number")
    USING "Absence Entry"."Absence Status" = 'APPROVED'
  ) AS "Workers on Approved Absence"
FROM "Absence Management"
WHERE
  "Absence Entry"."Absence Start Date"
    BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE)
        AND CURRENT_DATE
ORDER BY
  "Worker"."Department Name"

Example 3: Compensation with Grade Range Comparison

SELECT
  "Worker"."Worker Name",
  "Assignment"."Grade Name",
  "Salary"."Annual Salary",
  "Grade Rate"."Grade Midpoint",
  CASE
    WHEN "Salary"."Annual Salary"
           < "Grade Rate"."Grade Minimum"
      THEN 'Below Range'
    WHEN "Salary"."Annual Salary"
           > "Grade Rate"."Grade Maximum"
      THEN 'Above Range'
    ELSE 'In Range'
  END AS "Compa Status"
FROM "Compensation"
WHERE
  "Assignment"."Primary Assignment Flag" = 'Y'
ORDER BY
  "Compa Status", "Worker"."Worker Name"

Example 4: Turnover Report (Hire + Termination in One Query)

SELECT
  "Worker"."Department Name",
  FILTER(
    COUNT(DISTINCT "Worker"."Person Number")
    USING "Worker"."Action Name" = 'Hire'
      AND "Worker"."Action Date"
            >= TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)
  ) AS "YTD Hires",
  FILTER(
    COUNT(DISTINCT "Worker"."Person Number")
    USING "Worker"."Action Name" = 'Termination'
      AND "Worker"."Action Date"
            >= TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)
  ) AS "YTD Terminations"
FROM "Workforce Management"
ORDER BY
  "Worker"."Department Name"

Example 5: Payroll Actuals vs Budget (Multi-Measure)

SELECT
  "Payroll"."Cost Center",
  "Payroll"."Payroll Name",
  SUM("Payroll Results"."Result Value") AS "Total Payroll",
  FILTER(
    SUM("Payroll Results"."Result Value")
    USING "Element"."Classification Name" = 'Standard Earnings'
  ) AS "Regular Pay",
  FILTER(
    SUM("Payroll Results"."Result Value")
    USING "Element"."Classification Name" = 'Supplemental Earnings'
  ) AS "Supplemental Pay"
FROM "Payroll"
WHERE
  "Payroll Run"."Period Status" = 'COMPLETE'
ORDER BY
  "Payroll"."Cost Center"

Common Errors and Fixes

When to Use BI Publisher Instead of OTBI

OBIEE Logical SQL is powerful, but it has a ceiling. Use BI Publisher (BIP) when:

• You need a true outer join across data that doesn't share an RPD join path
• You're joining across two OTBI subject areas that have no common dimension in the same subject area
• Your report requires a physical SQL feature OBIEE Logical SQL doesn't support (window functions, lateral joins, recursive CTEs)
• You need row-level calculations that reference other rows (ranking, running totals across dimensions)

FAQ

Can I use aliases in OBIEE Logical SQL FROM?

No. OBIEE Logical SQL FROM takes a subject area name in quotes, not table aliases. Column references use "Folder"."Column" path notation. You cannot assign table aliases with AS in the FROM clause.

Does OBIEE support JOIN at all?

OBIEE Logical SQL supports a limited JOIN syntax for multi-fact queries (where you explicitly join two fact sources), but it is rarely used and has significant restrictions. For most HCM reports, the implicit join through the subject area is correct and preferred.

Why does adding a column from a second folder cause extra rows?

If the two presentation folders resolve to logical tables with a one-to-many join, adding the "many" side column fans out the results. This is the comma-join issue — the BI Server is correctly applying the join, but the grain of your query changed. Add COUNT(DISTINCT ...) or aggregate, or remove the low-grain column.

Can I use subqueries in OBIEE Logical SQL?

Yes — inline views and scalar subqueries are supported with restrictions. See our guide on OBIEE and OTBI Subquery Joins for syntax, nQSError fixes, and production examples.