Assertion Failure: pCriteria != 0
Root Cause & Fix

1. What Is pCriteria != 0?

The pCriteria != 0 assertion is an internal Oracle BI Server guard. pCriteria is a bitmask that tracks which filter conditions are "pushed down" to each physical data source for a given query. The assertion fires when the BI Server cannot assign at least one filter criterion to a particular Logical Table Source (LTS) — meaning it would have to cross-query two unrelated fact tables with no valid join path between them.

In plain English: you asked OBIEE to combine columns from two different fact tables that don't share a common dimension key in this query. OBIEE refuses rather than returning a Cartesian product.

2. The Full Error Message

The error surfaces in different wrappers depending on whether you hit it through Answers, OTBI, or a raw JDBC/ODBC connection. The internal assertion always reads the same:

3. Root Cause: Multi-Fact Queries

OBIEE's Logical SQL is translated to physical SQL by the BI Server in a two-phase process: first, it resolves which Logical Table Sources map to each column; second, it builds join paths between them. The pCriteria bitmask is the bookkeeping structure for step two.

When you select columns that map to two different fact LTS tables, the BI Server needs a shared dimension key to join them. If no such key is available in the current query context (because you didn't include the bridging dimension column, or because the RPD doesn't define the join), pCriteria stays at 0 for one of the LTS entries and the assertion fires.

The Three Most Common Multi-Fact Scenarios in Oracle HCM OTBI

4. Fix 1: Use a Single Fact Table

The cleanest fix is to scope your analysis to a single subject area and single fact. Ask: what is the primary question this report answers? Strip columns from the secondary fact entirely.

5. Fix 2: Add the Bridging Dimension Column

If both fact tables are joinable — they just lack the link in your current query — adding the shared dimension column to your criteria forces OBIEE to build the join path and resolves the assertion.

For most HCM + Payroll crosses, the bridge is Assignment ID:

-- Before fix: mixing headcount columns with pay run columns
-- This causes pCriteria != 0 because no join key is present
SELECT
    "Workforce Management - Workforce Trend"."Department"."Department Name",
    "Workforce Management - Workforce Trend"."Headcount"."Headcount",
    "Payroll - Payroll Run Results"."Element"."Element Name",
    "Payroll - Payroll Run Results"."Run Result Values"."Pay Value"
FROM "Workforce Management - Workforce Trend"

-- After fix: add the bridging assignment column
SELECT
    "Workforce Management - Workforce Trend"."Worker"."Assignment Number",  -- bridge key
    "Workforce Management - Workforce Trend"."Department"."Department Name",
    "Payroll - Payroll Run Results"."Run Result Values"."Pay Value"
FROM "Workforce Management - Workforce Trend"

6. Fix 3: Separate Reports + Union

When the two fact tables truly don't share a join path (no conforming key exists in the RPD), separate them into two individual analyses and union or link them at the presentation layer.

In OTBI Answers, create two analyses and use Combined Results (the Set Operations tab). In BI Publisher, create two separate data sets in the data model and join them using a correlated lookup element on Person Number.

7. Fix 4: Logical SQL EVALUATE Workaround

For advanced users with physical SQL access to the underlying DB, the EVALUATE function lets you push a subquery directly to the database, bypassing the BI Server's join logic entirely. Use this when you genuinely need combined data and can't change the RPD.

-- EVALUATE pushes a SQL fragment to the DB engine
-- The BI Server treats it as an opaque scalar — no pCriteria check
SELECT
    "Workforce Management - Workforce Trend"."Department"."Department Name",
    "Workforce Management - Workforce Trend"."Headcount"."Headcount",
    EVALUATE('(SELECT SUM(prv.result_value)
                FROM pay_run_result_values prv
                JOIN pay_assignment_actions paa
                  ON paa.assignment_action_id = prv.assignment_action_id
               WHERE paa.assignment_id = %1
                 AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date
                                        AND paa.effective_end_date)'
             AS DOUBLE,
             "Workforce Management - Workforce Trend"."Worker"."Assignment Id")
             AS pay_value
FROM "Workforce Management - Workforce Trend"

8. OTBI-Specific Notes (Oracle Cloud)

In Oracle Fusion Cloud (OTBI), you don't have direct RPD access, so Fixes 1–3 are the only options. The EVALUATE workaround requires your Cloud tenant to have EVALUATE enabled — Oracle disables it by default in SaaS because it allows arbitrary SQL execution against the HCM schema.

Cloud-specific triggers that look like pCriteria errors but have different fixes:

• Missing data role: If the user can see the column in the subject area but not the data behind it, OTBI may generate an empty LTS that triggers the assertion. Fix: assign the correct OTBI data role (e.g., Oracle Fusion Human Capital Management - Workforce Transaction Analysis Duty).
• Cross-pillar subject areas: Mixing HCM subject areas with ERP subject areas (e.g., General Ledger) always triggers pCriteria because the RPD pillars don't share conforming keys. Use separate analyses.
• Inactive LTS: After a Release Update, Oracle sometimes adds a new Logical Table Source and disables an old one. If a saved analysis still references the old LTS, the BI Server can't resolve pCriteria. Delete the affected column and re-add it from the current subject area tree.

9. Diagnosis Checklist

Run through this in order before rebuilding the report from scratch:

1. Count the subject areas — is your analysis pulling from more than one? That's almost always the cause.
2. Check the grain — are all columns at the same grain (person, assignment, pay period)? Mixed grains cause implicit fan-outs.
3. Add Person Number or Assignment Number — if you removed it for display reasons, add it back temporarily; if the error goes away, it was the missing bridge.
4. Check for deactivated columns — remove any column flagged with a yellow warning icon in the subject area tree and re-add it.
5. Review filters — a filter that references a column from a secondary fact is itself enough to trigger pCriteria. Move complex filters to a where-clause on a separate analysis.
6. Enable Query Log — in OBIEE Admin Tool: Manage → Sessions → Log Level 2. The nqquery.log will show exactly which LTS failed the pCriteria check.

Related Articles

• OBIEE Assertion Failure Errors: nQSError 10058 & 27002 Fix
• OTBI Comma Join: Root Cause & Fix
• nQSError 27042: "At Most One Row" Fix
• OBIEE Subquery Joins in OTBI
• OBIEE Federation Subject Area Conflicts