OBIEE nQSError 27042 — "At Most One Row Is Expected" Causes, Fixes & Diagnosis

OBIEE nQSError 27042 is one of the most common initialization block errors, but the error message is misleading. The full message reads "At most one row is expected in the result set for an initialization block SQL statement" — yet beginners often don't understand why their initialization block is returning multiple rows or what "scalar" means. This guide covers every root cause, diagnosis step, and copy-paste fix.

What nQSError 27042 Means

When an OBIEE initialization block runs, its SQL query populates one or more session variables. The error 27042 is thrown in two scenarios:

1. Init block SQL returns multiple rows: The block's SQL returns 2, 10, or 100 rows, but the session variable is configured to hold only 1 value (scalar type). The server cannot decide which row to use and throws 27042.
2. OTBI filter returns multiple matches: In Fusion Cloud OTBI, you have a calculated measure or filter condition that expects a single scalar value, but the underlying query returns 0 or many results instead of exactly 1.

The error message tells you which session variable caused the issue, but not why. The "why" is almost always one of these: (a) the SQL has no WHERE clause filtering to the current user, (b) the current user has multiple role assignments or department assignments, (c) the SQL is too broad and returns results for all users, or (d) you're using a scalar variable when you should use a row variable.

Root Causes of nQSError 27042

Cause 1: Init block SQL with no WHERE clause filtering to current user

The most common trigger. You query all users instead of just the current one. The SQL returns 100 rows, the scalar session variable can only hold 1, so OBIEE throws 27042.

-- WRONG: No WHERE clause — returns all departments (100+ rows)
SELECT DISTINCT department_id
FROM employees
ORDER BY department_id

-- RIGHT: Filter to current user
SELECT DISTINCT department_id
FROM employees
WHERE employee_id = VALUE(USER)
ORDER BY department_id

Cause 2: Init block SQL returns multiple rows per user

The WHERE clause correctly filters to the current user, but that user has multiple values. For example, a user assigned to 3 cost centers, or with 5 active role assignments. Each assignment is a row, so the query returns 3 or 5 rows, triggering 27042.

-- WRONG: User may have multiple cost center assignments (returns 3 rows)
SELECT cost_center_id
FROM employee_cost_centers
WHERE employee_id = VALUE(USER)

-- RIGHT: Use FETCH FIRST to grab the primary (first) one
SELECT cost_center_id
FROM employee_cost_centers
WHERE employee_id = VALUE(USER)
AND primary_flag = 'Y'
FETCH FIRST 1 ROWS ONLY

-- ALTERNATE: Use aggregate to collapse to single row
SELECT MAX(cost_center_id) AS cost_center_id
FROM employee_cost_centers
WHERE employee_id = VALUE(USER)
WHERE primary_flag = 'Y'

Cause 3: Correlated subquery returns multiple rows

Your init block uses a subquery in the SELECT clause that returns multiple rows. Scalar context (a single value in SELECT) cannot unpack multiple rows.

-- WRONG: Subquery can return multiple rows in scalar context
SELECT (SELECT role_id FROM user_roles WHERE user_id = VALUE(USER)) AS role
FROM dual

-- RIGHT: Collapse to a single value
SELECT LISTAGG(role_id, ',') WITHIN GROUP (ORDER BY role_id) AS roles
FROM user_roles
WHERE user_id = VALUE(USER)

Cause 4: Security group filter returns multiple matches

In OTBI, if you have a calculated measure or filter that uses a security group, and that group's definition returns multiple rows where it should return 1, you get 27042.

Cause 5: Using scalar variable when you need row variable

Your initialization block genuinely needs to return multiple values (e.g., all departments for the current user), but you incorrectly configured the session variable as scalar instead of row.

Scalar vs Row Session Variables Explained

Understanding the difference is critical to avoiding 27042.

If your init block SQL returns 3 rows (because the user has 3 cost centers), you must use a row variable, not a scalar. If you use a scalar variable, you get 27042 every time that initialization block runs.

How to Diagnose nQSError 27042

Follow these steps to identify the root cause before you fix anything:

1. Locate the init block: In OBIEE Administration Tool, go to Manage → Variables → Session Variables → Initialization Blocks. Find the block name mentioned in the error message (e.g., 'SECURITY_DEPARTMENT').
2. Extract the SQL: Open the init block definition and copy the entire SQL query.
3. Simulate the current user: Replace all VALUE(USER) with a hardcoded test user ID, e.g., 'EMP12345'.
4. Run it manually: Execute the SQL against the database (SQL Developer, SQL*Plus) using the same connection pool user that OBIEE uses.
5. Count the rows: If the query returns 0 rows, the session variable won't be set. If it returns > 1 rows, that's your 27042 trigger.
6. Identify the row count per user: Modify the query to show how many rows each user gets: SELECT user_id, COUNT(*) FROM ... GROUP BY user_id. If any user has count > 1, you've found the root cause.

5 Fix Patterns with Code Examples

Fix 1: Add FETCH FIRST 1 ROWS ONLY

If your SQL logically should return 1 row but accidentally returns multiples (due to duplicate joins or aggregation issues), use FETCH FIRST 1 ROWS ONLY to grab the first row and discard the rest.

-- Init block SQL: grab user's primary department
SELECT cc.cost_center_id
FROM employee_assignments ea
JOIN cost_centers cc ON ea.cost_center_id = cc.id
WHERE ea.employee_id = VALUE(USER)
AND ea.primary_assignment_flag = 'Y'
FETCH FIRST 1 ROWS ONLY

Fix 2: Use MAX() or MIN() to collapse multiple rows

If multiple rows represent the same logical entity (e.g., multiple role assignments), use an aggregate function to pick one deterministically.

-- Init block SQL: grab user's highest-priority role (by role_id)
SELECT MAX(role_id) AS role_id
FROM user_roles
WHERE user_id = VALUE(USER)
AND role_status = 'ACTIVE'
GROUP BY user_id

Fix 3: Use LISTAGG() to concatenate multi-value results

If you genuinely need multiple values in a single result (e.g., all departments the user can access), use LISTAGG() to concatenate them into a comma-separated list, then store in a scalar variable. When you use the variable in a filter, the filter expands the list.

-- Init block SQL: all departments for user as comma-separated list
SELECT LISTAGG(department_id, ',') WITHIN GROUP (ORDER BY department_id) AS accessible_depts
FROM user_department_access
WHERE user_id = VALUE(USER)
AND access_type = 'VIEW'

Fix 4: Change to a ROW session variable

If you genuinely need multiple rows (and your init block is configured correctly to return multiple rows), change the session variable from scalar to row type.

-- In Administration Tool, for session variable 'USER_DEPARTMENTS':
-- 1. Go to Variables > Session Variables > USER_DEPARTMENTS
-- 2. Uncheck "Is Session Variable Scalar?"
-- 3. Check "Is Session Variable Row?"
-- 4. Set initialization block to return multiple rows (no FETCH FIRST, no MAX)
-- 5. Save and reload the server

-- Init block SQL:
SELECT DISTINCT department_id
FROM user_department_access
WHERE user_id = VALUE(USER)
AND access_type = 'VIEW'

Fix 5: Add a WHERE clause filtering to current user

If the init block SQL has no WHERE clause at all, that's the bug. Add one to filter to the current user.

-- WRONG: No WHERE clause — all 1000 users' departments!
SELECT DISTINCT department_id FROM employees

-- RIGHT: Add WHERE clause
SELECT DISTINCT department_id
FROM employees
WHERE employee_id = VALUE(USER)

Init Block Types Quick Reference Table

Use this table when you're setting up a new initialization block to avoid 27042 before it happens:

nQSError 27042 in OTBI (Oracle Fusion Cloud)

In OTBI, you don't write initialization blocks (that's OBIEE-specific). However, you can still hit 27042 in two scenarios:

Scenario 1: Calculated measure returns multiple rows

You have a calculated measure that tries to return a scalar result (a single number), but the underlying query returns multiple rows or a query error. OTBI throws 27042 when it tries to assign the multi-row result to a scalar field.

-- WRONG: This calculated measure queries employees and gets 100 rows
SELECT SUM(salary)
FROM employees
-- No WHERE clause — returns multiple rows, breaks scalar context

-- RIGHT: Wrap in aggregation to ensure scalar result
SELECT SUM(salary)
FROM employees
WHERE department_id = @department_id  -- Parameter passed in
AND employee_status = 'ACTIVE'

Scenario 2: Filter or prompt uses a subquery returning multiple values

You have a column filter or dashboard prompt that uses a subquery to populate allowed values. If that subquery is supposed to return a single scalar but returns multiple rows, you get 27042.

Debug Using BI Server Log Level 3

When you're stuck, enable BI Server logging at level 3 (detailed query logging). This shows the exact SQL OBIEE is executing, including all VALUE(USER) substitutions.

Steps (OBIEE on-premises):

1. Open Administration Tool
2. Go to Manage → Servers → BI Server → Click "Logging" tab
3. Set Query Log Level to 3 (or higher for more detail)
4. Click "Save and Deploy"
5. Run a report or dashboard that hits the init block (or try to log in)
6. Go to the BI Server log location (usually /config/logfiles/ or /logs/): Find nqserver.log
7. Search for the initialization block name or the SQL you're testing
8. Look at the actual SQL sent to the database — this is what's returning multiple rows

Frequently Asked Questions

Q: I'm getting 27042 but my init block SQL looks correct. What else could it be?

Check three things: (1) The SQL you're looking at in the Administration Tool might not be the latest — restart the BI Server and try again. (2) The initialization block might not be the problem — check if there's another init block that runs before it that's failing. (3) The connection pool user (who OBIEE uses to run the SQL) might have different data access than your user account — test the SQL using the connection pool user's login, not your own.

Q: How do I use a row session variable in a filter in OTBI?

If you configure a session variable as row type and populate it with multiple values (e.g., ['DEPT_001', 'DEPT_002', 'DEPT_003']), you can use it in a filter with the IN operator: "Department"."Department ID" IN (VALUE(ROW_VARIABLE_NAME)). OBIEE expands the row variable into a comma-separated list automatically.

Q: My init block query returns 1 row in SQL Developer but still throws 27042 in OBIEE. Why?

Three possibilities: (1) You tested with your own login; OBIEE is using the connection pool user, who may see different data. (2) You tested with a hardcoded user ID, but OBIEE is logging in a different user. (3) The init block runs for multiple users in parallel, and one user's query is returning > 1 rows while yours returns 1. Check which user is logged in when the error occurs, then test the init block SQL for that specific user.

Q: Is there a way to defer initialization blocks or run them in a specific order?

In OBIEE Administration Tool, you can set dependencies between init blocks using the "Initialization Block Sequence" setting. If Block B relies on a session variable set by Block A, configure Block B to depend on Block A. OBIEE will run Block A first. However, there's no way to "skip" an init block if it's not applicable for a user — if a user triggers 27042, they can't log in.

Q: Can I catch 27042 in a filter or use IFNULL to handle it?

No. nQSError 27042 happens during session initialization, before any queries run. Once you're in a dashboard or report, the session variable is either set or it's not. You can't recover from 27042 with a filter condition — you have to fix the initialization block SQL. If you want optional session variables (that might not be set), configure them with "Allow NULL values" in the initialization block definition.

Quick Diagnostic Checklist for 27042

1. Find the initialization block name in the error message
2. Extract the SQL query from Administration Tool
3. Replace VALUE(USER) with a test user ID
4. Run the SQL manually — count the rows
5. If > 1 rows, identify which rows: do they belong to one user (multiple assignments) or multiple users (missing WHERE clause)?
6. If multiple assignments per user, add FETCH FIRST 1 ROWS ONLY or use MAX() / LISTAGG()
7. If missing WHERE clause, add one: WHERE [user_column] = VALUE(USER)
8. If you genuinely need multiple rows, change the session variable from scalar to row type
9. Restart BI Server and test login again