An exception report suddenly shows no invoices: check a null in the exclusion query

A daily exception report that falls from several unresolved invoices to zero may indicate successful processing. It can also indicate a query that stopped returning the exceptions. When the change is unexpected, preserve the last populated report and inspect the filtering logic before announcing that every invoice has been handled. The absence of rows is a result to explain, not automatic evidence of completed work.

One specific cause is a NOT IN comparison against a list containing a null. PostgreSQL documents that an unmatched left-hand value can produce an unknown result when at least one right-hand value is null. Its WHERE clause keeps true conditions and discards false or null conditions. Those two behaviors can remove the very unmatched records the report was meant to show.

This is a query-semantics investigation. It does not imply that every blank identifier causes the same behavior in every tool. Ask the analyst to identify the actual database, expression and comparison population. A spreadsheet filter or a different reporting expression needs its own verified explanation rather than a diagnosis borrowed from a superficially similar SQL example.

Suppose the source register contains invoice references 101, 102 and 103, with amounts of $250, $400 and $600. The processed-reference table contains 101 and one null reference from an incomplete import. The intended report should identify 102 and 103 as not yet matched, representing two invoices and $1,000 requiring investigation.

With the condition invoice_id NOT IN the processed-reference subquery, 101 is excluded because it matches. For 102 and 103, the null in the comparison set prevents the expression from establishing the intended true result. The filtered output can therefore contain no invoices even though two references have no valid match. The missing $1,000 has not been processed merely because the exception screen is empty.

Have the analyst reproduce this small case in an approved test environment and show the intermediate comparison values. Then locate the equivalent null in the real comparison population through restricted read access. The demonstration should connect the observed technical behavior to the actual defect, not become a general explanation attached to an unrelated reporting incident.

Write the matching rule in business terms. A record may count as processed only when the same invoice belongs to the same entity and has reached the required completion stage. A reference present in an error table should not automatically count as a successful posting. Include the relevant source system and record version if those dimensions are necessary to distinguish the work.

Separate a null in the exclusion table from a missing identifier in the source invoice. The first can disrupt the comparison. The second means the source record itself cannot be matched using the intended key. That record needs a visible exception treatment, even if a revised query would otherwise include it as unmatched.

Review whether identifiers are unique only within an entity or provider account. Invoice 101 in one company is not necessarily invoice 101 in another. Replacing NOT IN with a different expression will not repair an incomplete business key. The developer needs the full match condition that finance intends the report to enforce.

A developer may choose an explicit NOT EXISTS condition expressing the absence of a qualifying matching row. PostgreSQL defines EXISTS by whether the subquery returns any rows. That provides a way to test the intended relationship directly, but the conditions inside the subquery still need review. The name of the operator alone does not guarantee the right business result.

Another narrowly appropriate repair may filter nulls from a comparison set whose valid identifiers are already well defined. Have the implementation owner explain why that approach fits the schema and intended source-key treatment. Do not replace every missing identifier with zero or a fabricated code merely to make the original expression return rows.

Retain the incomplete processed-reference record as its own data-quality issue. Removing it from the membership test should not make its failed import disappear from operational review. Assign the source defect to an owner and determine whether other records from that import are affected. Query repair and source-record repair can be necessary parts of the same incident.

Test a confirmed processed invoice, a genuinely unmatched invoice, a null in the comparison population and a missing source key. Include an invoice with the same local reference in another entity if that is possible in the design. State the expected treatment of each case before reviewing the output so the team cannot quietly redefine success around whatever the query happens to return.

For the illustrative three-invoice set, the repaired unmatched list should contain 102 and 103 once each, totaling $1,000. The processed invoice 101 should remain excluded under the stated rule. Report the null reference separately. Checking only that the exception count is greater than zero would leave room for another error, such as returning the processed invoice as well.

Compare the real repaired population with the original source and processing evidence for a fixed period. Identify which invoices were hidden by the defect and which were already visible. Check whether collection work, posting follow-up or another operational action was delayed. Give the affected workflow owner the recoverable record list rather than asking them to infer it from a revised dashboard total.

Keep the query version, match definition, source completeness check and repair evidence with the report. A future zero should be supported by the expected source population and completed matching, with invalid identifiers still visible through their own route. An empty exception table and an empty source extract should never be presented as the same successful outcome.

Review how the null entered the comparison table and whether a relevant schema or ingestion control can prevent a recurrence. Apply the control where the missing value is actually invalid. Some fields legitimately permit nulls, so a broad prohibition would conceal the distinction the investigation has just established.