A SQL report can return valid results while assigning the wrong labels. The query runs, every row receives a category, and no database error appears. The problem may be the order of conditions inside a CASE expression.
This is particularly easy to miss when rules overlap. A record that qualifies for a high-priority category may also satisfy a broader, lower-priority condition. Oracle does not choose the label that sounds most appropriate. It follows the expression you wrote.
The first matching condition wins
A searched CASE expression evaluates conditions in order and returns the result associated with the first true condition. Later conditions are not evaluated after that match. If nothing matches, Oracle returns the ELSE result, or NULL when no ELSE is supplied.
Consider a hypothetical operations queue. Items waiting at least 120 minutes should be marked “Urgent.” Items waiting at least 30 minutes should be marked “Review.”
If the 30-minute condition appears first, an item waiting 180 minutes matches it immediately. The urgent rule is never reached for that row.
An appropriate ordering is:
CASE
WHEN wait_minutes IS NULL THEN 'Unknown'
WHEN wait_minutes < 0 THEN 'Invalid'
WHEN wait_minutes >= 120 THEN 'Urgent'
WHEN wait_minutes >= 30 THEN 'Review'
ELSE 'Routine'
END AS queue_statusThe example deliberately handles missing and invalid values before applying the ordinary classification rules.
Decide what missing data means
A missing measurement and a small measurement should not automatically receive the same business interpretation.
In this example, an unknown waiting time is not evidence that the item is routine. Giving missing values their own label makes that distinction visible to whoever reads the report.
Oracle documents IS NULL and IS NOT NULL as the conditions for testing null values. Use that explicit test rather than attempting to identify missing data with ordinary equality to NULL.
The label itself remains a business decision. Another team might prefer “Needs investigation” or might exclude incomplete records from a particular operational view. The SQL should reflect that decision clearly.
Test the boundaries, not just typical rows
A useful review set for this example includes missing data, a negative value, zero, 29, 30, 119, 120, and 180 minutes.
These values answer different questions. Thirty verifies the beginning of the review band. One hundred twenty verifies the urgent threshold. One hundred eighty confirms that an overlapping higher value reaches the intended branch.
Write expected labels beside the test inputs before running the query. Otherwise, it is easy to rationalize the output after seeing it.
Also consider what happens when the policy changes. If the urgent threshold moves, the relevant test cases should move with it.
Keep business rules readable
A compact expression is useful only when readers can understand its priorities. Prefer clear thresholds and meaningful labels over clever arrangements that require mental reconstruction.
For an expression like this one, place exceptional cases first and overlapping thresholds in the order that expresses the intended priority. Include an explicit ELSE when a fallback category is part of the design.
A reviewer should be able to read the conditions from top to bottom and explain why each sample record receives its label. That is a stronger check than merely confirming that the query executes successfully.