Does OBIEE logical SQL support subqueries?

Yes. OBIEE logical SQL supports inline views (derived tables in FROM), scalar subqueries in SELECT or WHERE, and correlated subqueries with EXISTS and IN. However, subqueries must reference presentation columns from your selected subject area, not physical table names. All inline views require an alias.

Why does my OTBI subquery return nQSError 27002?

The most common causes are: 1) Missing alias on an inline view (derived table), 2) Subquery without parentheses or wrong parenthesis placement, 3) Mixing physical table names with logical presentation columns in the same query. Always use presentation layer column paths like "Folder"."Column" inside OTBI subqueries.

How do I join data from two different OTBI subject areas?

OTBI logical SQL queries run against a single subject area at a time. To combine data from two subject areas: 1) Use a BI Publisher data model with two separate OTBI queries as datasets, then join them by Person Number or another key in the BIP layer, or 2) If both subject areas share overlapping columns, check if a single broader subject area already exists that spans both.

OBIEE and OTBI Subquery Joins — Complete Guide with Logical SQL Examples

OBIEE and OTBI subqueries are powerful but differ significantly from standard SQL. Many developers coming from SQL backgrounds expect all subquery patterns to work—but they don't. This guide covers every supported subquery pattern, common errors, and practical workarounds for Oracle HCM.

Key difference: OBIEE logical SQL subqueries reference presentation columns from your selected subject area, not physical table names. This is the single most important rule. A subquery that runs fine in SQL*Plus will fail in OTBI because it references table names instead of presentation folder paths.

OBIEE Logical SQL vs Physical SQL: Subquery Support Overview

OBIEE's BI Server parses your logical SQL and converts it to physical database SQL before execution. When you write a subquery in logical SQL, you're not talking to the database—you're talking to the presentation layer. This fundamental difference explains why many standard SQL patterns break.

What IS supported:

Inline views (derived tables in FROM clause) — yes, with mandatory alias
Correlated subqueries with EXISTS, NOT EXISTS, IN — yes, using presentation columns
Scalar subqueries in SELECT or WHERE returning single value — yes
Window functions (RANK, ROW_NUMBER) — yes, in most OBIEE/OTBI versions
CASE expressions with subqueries — yes

What IS NOT supported:

Comma-separated FROM with subqueries (must use explicit JOIN syntax)
UNION inside subqueries (in older OBIEE versions; newer versions support it)
Correlated subqueries that reference tables outside the FROM clause subject area
Physical table names or aliases in subqueries (must use presentation layer paths)
Subqueries without aliases in FROM clause (will trigger nQSError 27002)

Inline Views (Derived Tables) in FROM Clause

Inline views are subqueries in the FROM clause, also called "derived tables." OBIEE supports them, but every inline view must have an alias. Missing the alias is the #1 cause of "nQSError 27002 near ) syntax error."

Syntax: Basic inline view

-- Correct: inline view with mandatory alias
SELECT
  inner_query.person_number,
  inner_query.full_name,
  inner_query.assignment_count
FROM (
  SELECT
    "Worker Details"."Person Number"  AS person_number,
    "Worker"."Full Name"              AS full_name,
    COUNT(*)                          AS assignment_count
  FROM "Workforce Management - Worker Assignment Real Time"
  WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
  GROUP BY "Worker Details"."Person Number", "Worker"."Full Name"
) inner_query
ORDER BY inner_query.assignment_count DESC

Notice that the subquery references presentation columns (like "Worker Details"."Person Number"), not physical table names. The alias inner_query is required.

Why the alias is mandatory

OBIEE's parser needs a name for the inline view to reference columns from it in the outer query. Without the alias, the parser cannot bind inner_query.person_number to a result set. The error message is terse—just "nQSError 27002 near )"—but it's always the missing alias.

nQSError: 27002

Near <)>: Syntax error [at line 5, near char 182]

Fix

Add AS alias_name after the closing parenthesis of your subquery. The alias name can be anything: sub1, derived_employees, t1—pick a descriptive name for readability.

Inline views with aggregation

-- Count employees per department from a derived table
SELECT
  dept.department_name,
  dept.total_employees
FROM (
  SELECT
    "Department"."Department Name"  AS department_name,
    COUNT(DISTINCT "Worker Details"."Person Number")  AS total_employees
  FROM "Workforce Management - Worker Assignment Real Time"
  WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
  GROUP BY "Department"."Department Name"
) dept
ORDER BY dept.total_employees DESC

Correlated Subqueries: EXISTS, NOT EXISTS, IN

Correlated subqueries allow you to filter outer rows based on conditions in the subquery. OBIEE supports EXISTS, NOT EXISTS, and IN patterns, but they must reference the outer query's columns correctly through the presentation layer.

EXISTS pattern

Use EXISTS when you need to check if at least one row matching a condition exists. EXISTS returns true/false, not a value.

-- Find all active employees who have had assignment changes in the last 90 days
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Assignment Details"."Effective Start Date"
FROM "Workforce Management - Worker Assignment Real Time" outer_asgn
WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
AND EXISTS (
  SELECT 1
  FROM "Workforce Management - Worker Assignment Real Time" history
  WHERE history."Worker Details"."Person Number" = outer_asgn."Worker Details"."Person Number"
  AND history."Assignment Details"."Effective Start Date" >= CURRENT_DATE - 90
)

Note the WHERE condition inside the subquery: history."Worker Details"."Person Number" = outer_asgn."Worker Details"."Person Number". This correlates the subquery to the outer query by matching person numbers.

NOT EXISTS pattern

Invert the logic to find rows where NO matching condition exists.

-- Find employees with NO assignment in the last 30 days (potentially inactive)
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Last Hire Effective Date"
FROM "Workforce Management - Worker Assignment Real Time" p
WHERE NOT EXISTS (
  SELECT 1
  FROM "Workforce Management - Worker Assignment Real Time" a
  WHERE a."Worker Details"."Person Number" = p."Worker Details"."Person Number"
  AND a."Assignment Details"."Effective Start Date" >= CURRENT_DATE - 30
)

IN pattern with subquery

Use IN when you need a subquery that returns a single column of values to match against.

-- Find all employees whose department's headcount exceeds 50
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Department"."Department Name"
FROM "Workforce Management - Worker Assignment Real Time"
WHERE "Department"."Department Name" IN (
  SELECT
    "Department"."Department Name"
  FROM "Workforce Management - Worker Assignment Real Time"
  WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
  GROUP BY "Department"."Department Name"
  HAVING COUNT(DISTINCT "Worker Details"."Person Number") > 50
)

Performance note: IN subqueries can be slow in OBIEE if the subquery returns many rows. EXISTS is often faster for row existence checks. Test both and use execution statistics to compare.

Scalar Subqueries in SELECT and WHERE

A scalar subquery returns exactly one value (one row, one column). Use them in SELECT to add calculated columns, or in WHERE to filter by a single value.

Scalar subquery in SELECT clause

-- Add company average headcount to each employee's row
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Department"."Department Name",
  (SELECT COUNT(DISTINCT "Worker Details"."Person Number")
   FROM "Workforce Management - Worker Assignment Real Time"
   WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
  ) AS company_total_headcount
FROM "Workforce Management - Worker Assignment Real Time"
WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'

This subquery runs once and returns the same value for every row, so company_total_headcount will be identical in all rows.

Scalar subquery in WHERE clause

-- Find all employees whose salary exceeds the company average salary
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Annual Salary"
FROM "Compensation Real Time"
WHERE "Annual Salary" > (
  SELECT AVG("Annual Salary")
  FROM "Compensation Real Time"
)

Common error

nQSError: 27002 "Near <)>: Syntax error" if your scalar subquery returns multiple rows or columns

If your scalar subquery accidentally returns 2+ rows or 2+ columns, OBIEE will reject it as a syntax error because a scalar position expects exactly one value.

Why Physical SQL Subquery Patterns Fail in OBIEE Logical SQL

The biggest gotcha: standard SQL subqueries reference physical table names. OBIEE subqueries reference presentation folder and column names. Here's a concrete example:

Physical SQL (works in SQL*Plus, FAILS in OTBI):

-- This works in SQL*Plus but will FAIL in OTBI:
SELECT person_id, name
FROM per_all_people_f p
WHERE SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
AND EXISTS (
  SELECT 1 FROM per_all_assignments_f a
  WHERE a.person_id = p.person_id
  AND a.effective_latest_change = 'Y'
)

Logical SQL (OBIEE/OTBI way):

-- Correct OBIEE/OTBI logical SQL version:
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name"
FROM "Workforce Management - Worker Assignment Real Time" p
WHERE EXISTS (
  SELECT 1
  FROM "Workforce Management - Worker Assignment Real Time" a
  WHERE a."Worker Details"."Person Number" = p."Worker Details"."Person Number"
  AND a."Assignment Details"."Effective Latest Change" = 'Yes'
)

Key differences:

Table names gone: No per_all_people_f, no per_all_assignments_f. Instead, one subject area: "Workforce Management - Worker Assignment Real Time".
Column paths qualified: Instead of person_id, it's "Worker Details"."Person Number" — full folder.column notation.
Date logic implicit: OBIEE automatically handles SYSDATE BETWEEN effective_start_date AND effective_end_date through the metadata. You don't write it in logical SQL for temporal tables.
Function names changed: SYSDATE becomes CURRENT_DATE; 'Y' might become 'Yes' depending on the presentation layer's lookup codes.

Common nQSError Codes with Subqueries

nQSError: 27002 "Near ) Syntax error"

Usually caused by:

Missing alias on an inline view: SELECT * FROM (...) instead of SELECT * FROM (...) AS alias
Subquery with wrong parenthesis count: SELECT * FROM (SELECT ...) vs SELECT * FROM ((SELECT ...))
Scalar subquery returning multiple rows

nQSError: 22024 "Column not found in subject area"

Your subquery references a column that doesn't exist in the selected subject area. Always verify column paths against the OTBI column browser before writing the query.

nQSError: 14025 "No fact table at requested level"

You're mixing grain levels from two incompatible subject areas inside a single query. OBIEE cannot automatically roll up or drill down between different subject areas. Use the multi-dataset workaround (see below).

nQSError: 96015 "Function called with wrong number of arguments"

Your subquery uses an OBIEE function with the wrong argument count. For example, IFNULL(col) needs 2 arguments: IFNULL(col, default_value).

Multi-Dataset Workaround: Joining Two Subject Areas

OBIEE logical SQL queries run against one subject area at a time. You cannot write a single query that joins two subject areas—the BI Server will throw nQSError: 27002 "syntax error near comma" if you try.

If you need data from two subject areas, use a BI Publisher data model with two separate OTBI logical SQL datasets, then join them in the BIP layer.

Example: Combining Worker data + Absence balances

Worker data lives in Workforce Management - Worker Assignment Real Time. Absence balances live in Workforce Management - Accrual Real Time. These are two separate subject areas.

Solution: BI Publisher data model with 2 datasets

-- DATASET 1: Worker details (Subject Area 1)
SELECT
  "Worker Details"."Person Number"     AS person_number,
  "Worker"."Full Name"                 AS full_name,
  "Department"."Department Name"       AS department,
  "Annual Salary"                      AS salary
FROM "Workforce Management - Worker Assignment Real Time"
WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'

-- DATASET 2: Absence balances (Subject Area 2)
SELECT
  "Worker"."Person Number"             AS person_number,
  "Absence Plan"."Absence Plan Name"   AS absence_type,
  "Balance"."Balance in Days"          AS balance_days
FROM "Workforce Management - Accrual Real Time"

-- In BI Publisher data model: Define a group link joining dataset1.person_number = dataset2.person_number
-- Then in the RTF template, you can display workers with their absence balances using nesting

This approach lets you fetch data from both subject areas, then join them at the reporting layer where you have full control.

EVALUATE() Function for Native DB SQL

If your OBIEE/OTBI instance has direct database access enabled (rare in cloud), you can use EVALUATE() to push native SQL to the physical database layer.

Syntax:

SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  EVALUATE(
    'SELECT AVG(salary) FROM per_all_people_f WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date'
  ) AS avg_salary
FROM "Workforce Management - Worker Assignment Real Time"

Caution: EVALUATE() is disabled in most cloud tenants for security reasons. If you attempt to use it on OTBI and get an error, contact Oracle Support to check if it's enabled in your instance.

Window Functions and RANK()

OBIEE supports window functions like RANK(), ROW_NUMBER(), DENSE_RANK() in logical SQL. These are useful for ranking rows within groups.

RANK() to find top earners per department

-- Rank employees by salary within each department, show top 3
SELECT
  "Department"."Department Name",
  "Worker"."Full Name",
  "Annual Salary",
  RANK() OVER (PARTITION BY "Department"."Department Name" ORDER BY "Annual Salary" DESC) AS salary_rank
FROM "Compensation Real Time"
WHERE RANK() OVER (PARTITION BY "Department"."Department Name" ORDER BY "Annual Salary" DESC) <= 3
ORDER BY "Department"."Department Name", salary_rank

This query ranks salaries within each department and returns only the top 3 earners per department.

ROW_NUMBER() for unique row numbering

-- Get the latest assignment per person
SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Assignment Details"."Job Name",
  "Assignment Details"."Effective Start Date"
FROM (
  SELECT
    "Worker Details"."Person Number",
    "Worker"."Full Name",
    "Assignment Details"."Job Name",
    "Assignment Details"."Effective Start Date",
    ROW_NUMBER() OVER (PARTITION BY "Worker Details"."Person Number" ORDER BY "Assignment Details"."Effective Start Date" DESC) AS rn
  FROM "Workforce Management - Worker Assignment Real Time"
) latest_asgn
WHERE latest_asgn.rn = 1

Practical Examples for Oracle HCM

Example 1: Top 5 highest-paid employees

SELECT
  "Worker"."Full Name",
  "Job"."Job Name",
  "Department"."Department Name",
  "Annual Salary"
FROM "Compensation Real Time"
ORDER BY "Annual Salary" DESC
FETCH FIRST 5 ROWS ONLY

Example 2: Employees with no assignment in last 90 days (NOT EXISTS pattern)

SELECT
  "Worker Details"."Person Number",
  "Worker"."Full Name",
  "Worker"."Hire Date"
FROM "Workforce Management - Worker Assignment Real Time" p
WHERE NOT EXISTS (
  SELECT 1
  FROM "Workforce Management - Worker Assignment Real Time" a
  WHERE a."Worker Details"."Person Number" = p."Worker Details"."Person Number"
  AND a."Assignment Details"."Effective Start Date" >= CURRENT_DATE - 90
)
ORDER BY p."Worker"."Hire Date" DESC

Example 3: Count employees per department vs company average (scalar subquery)

SELECT
  "Department"."Department Name",
  COUNT(DISTINCT "Worker Details"."Person Number") AS dept_headcount,
  (SELECT COUNT(DISTINCT "Worker Details"."Person Number")
   FROM "Workforce Management - Worker Assignment Real Time"
   WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
  ) AS company_total,
  ROUND(
    100.0 * COUNT(DISTINCT "Worker Details"."Person Number") /
    (SELECT COUNT(DISTINCT "Worker Details"."Person Number")
     FROM "Workforce Management - Worker Assignment Real Time"
     WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
    ),
    2
  ) AS pct_of_total
FROM "Workforce Management - Worker Assignment Real Time"
WHERE "Assignment Details"."Assignment Status Type" = 'ACTIVE'
GROUP BY "Department"."Department Name"
ORDER BY dept_headcount DESC

Example 4: Latest assignment per person with effective date

SELECT
  latest."Worker Details"."Person Number",
  latest."Worker"."Full Name",
  latest."Job"."Job Name",
  latest."Assignment Details"."Effective Start Date",
  latest."Department"."Department Name"
FROM (
  SELECT
    "Worker Details"."Person Number",
    "Worker"."Full Name",
    "Job"."Job Name",
    "Assignment Details"."Effective Start Date",
    "Department"."Department Name",
    ROW_NUMBER() OVER (
      PARTITION BY "Worker Details"."Person Number"
      ORDER BY "Assignment Details"."Effective Start Date" DESC
    ) AS row_num
  FROM "Workforce Management - Worker Assignment Real Time"
) latest
WHERE latest.row_num = 1
ORDER BY latest."Assignment Details"."Effective Start Date" DESC

Subquery Pattern Quick Reference

Pattern	Use Case	OBIEE Support	Common Errors
`FROM (SELECT ...) alias`	Inline view / derived table	Full support	Missing alias → nQSError 27002
`WHERE EXISTS (SELECT ...)`	Check if row exists	Full support	Correlation not matching → 0 results
`WHERE NOT EXISTS (SELECT ...)`	Check if row does NOT exist	Full support	Same as EXISTS
`WHERE col IN (SELECT ...)`	Match against subquery results	Full support	Subquery returns 2+ columns → error
`SELECT (SELECT ...) AS col`	Add scalar column from subquery	Full support	Subquery returns 2+ rows → error
`WHERE col > (SELECT ...)`	Filter by scalar value	Full support	Same as SELECT scalar
`RANK() OVER (PARTITION BY ...)`	Rank rows within group	Full support	Window function syntax errors
`FROM sa1, sa2 WHERE ...`	Join two subject areas	NOT supported	nQSError 27002 near comma
`EVALUATE('native_sql')`	Push native DB SQL	Conditional (security)	Often disabled in cloud

FAQ: OBIEE Subquery Questions

Q1: Can I use UNION in a subquery?

A: Newer OBIEE versions (11g+) support UNION inside subqueries, but older versions (10g) do not. If you get "nQSError: 27002" when using UNION inside a subquery, your instance doesn't support it. Workaround: run two separate OTBI queries and union the results in a reporting tool like BI Publisher or Tableau instead.

Q2: Why does my correlated subquery return zero rows?

A: Check your correlation condition in the WHERE clause. Make sure the column names match exactly. For example: a."Worker Details"."Person Number" = p."Worker Details"."Person Number" must use identical folder and column paths on both sides. If one side has a typo, the join will match nothing.

Q3: Can I use a subquery in a CASE expression?

A: Yes. CASE expressions can contain scalar subqueries:

SELECT
  "Worker"."Full Name",
  CASE
    WHEN "Annual Salary" > (SELECT AVG("Annual Salary") FROM "Compensation Real Time")
    THEN 'Above Average'
    ELSE 'Below Average'
  END AS salary_tier
FROM "Compensation Real Time"

OBIEE Logical SQL vs Physical SQL: Subquery Support Overview

What IS supported:

What IS NOT supported:

Inline Views (Derived Tables) in FROM Clause

Syntax: Basic inline view

Why the alias is mandatory

Inline views with aggregation

Correlated Subqueries: EXISTS, NOT EXISTS, IN

EXISTS pattern

NOT EXISTS pattern

IN pattern with subquery

Scalar Subqueries in SELECT and WHERE

Scalar subquery in SELECT clause

Scalar subquery in WHERE clause

Why Physical SQL Subquery Patterns Fail in OBIEE Logical SQL

Physical SQL (works in SQL*Plus, FAILS in OTBI):

Logical SQL (OBIEE/OTBI way):

Key differences:

Common nQSError Codes with Subqueries

nQSError: 27002 "Near ) Syntax error"

nQSError: 22024 "Column not found in subject area"

nQSError: 14025 "No fact table at requested level"

nQSError: 96015 "Function called with wrong number of arguments"

Multi-Dataset Workaround: Joining Two Subject Areas

Example: Combining Worker data + Absence balances

Solution: BI Publisher data model with 2 datasets

EVALUATE() Function for Native DB SQL

Syntax:

Window Functions and RANK()

RANK() to find top earners per department

ROW_NUMBER() for unique row numbering

Practical Examples for Oracle HCM

Example 1: Top 5 highest-paid employees

Example 2: Employees with no assignment in last 90 days (NOT EXISTS pattern)

Example 3: Count employees per department vs company average (scalar subquery)

Example 4: Latest assignment per person with effective date

Subquery Pattern Quick Reference

FAQ: OBIEE Subquery Questions

Q1: Can I use UNION in a subquery?

Q2: Why does my correlated subquery return zero rows?

Q3: Can I use a subquery in a CASE expression?

OBIEE/OTBI SQL Validator

Stop Writing OTBI Reports From Scratch

Related guides