How do I understand an old Oracle report query nobody documented?

Break it at the boundaries first. Legacy Oracle reports are usually a stack of inline views and a long SELECT list, and each inline view answers one narrow question. Name each block by what it returns rather than by how it works, then read the outer query. Pay attention to DECODE and NVL, which encode business rules invisibly, and to ROWNUM, which behaves differently depending on where it appears. Pasting the query into an explainer gives you the same block-by-block reading in seconds, using your own table names.

What are the most common bugs in legacy Oracle SQL?

An INNER JOIN buried in a chain of outer joins, which silently drops rows; NVL hiding a NULL that should have been investigated; ROWNUM applied before ORDER BY, so you get ten arbitrary rows rather than the top ten; implicit date conversion that depends on session NLS settings and breaks when the job runs from a different client; and duplicated rows from a join on a non-unique key inflating every SUM below it.

Why does a legacy query return different numbers than last year?

Usually nothing in the query changed — the data did. A new value appeared in a column that a DECODE does not handle and falls into the default branch, or a previously unique key stopped being unique, or a status code was reused. Compare row counts at each join step against a known-good period; the step where the counts diverge is where the assumption broke.

Is ROWNUM safe to use for top-N queries?

Not directly. Oracle assigns ROWNUM as rows are produced, before ORDER BY runs, so WHERE ROWNUM <= 10 ORDER BY revenue DESC returns ten arbitrary rows sorted, not the top ten. The correct form wraps the ordered query in an inline view and filters ROWNUM outside it, or uses FETCH FIRST 10 ROWS ONLY on 12c and later. This is one of the most common real bugs in old Oracle reports.

OracleLegacy

How to Understand and Debug Legacy Oracle SQL

It was written in 2011, it feeds the month-end close, and the author left in 2019. Nobody wants to touch it. Here is how to work on it anyway.

Aug 11, 202610 min

What legacy Oracle SQL looks like

Reports that have survived a decade share a shape: a long SELECT list full of DECODE and NVL, several inline views in the FROM clause, a WHERE clause with a mix of join conditions and filters, and somewhere in the middle a ROWNUM that nobody remembers adding.

None of that is bad code. It is code that accumulated business rules, one exception at a time, from people who each solved one problem and left.

Read it as blocks, not as lines

Split at every inline view and CTE boundary. Give each block a one-line name describing what it returns — "one row per invoice with the tax recalculated", "open orders as of the cutoff". Six blocks with names are readable; 400 lines are not.

Then read the outer query using those names. Most legacy reports turn out to be three ideas stacked, not forty.

The five things that are usually wrong

1. ROWNUM before ORDER BY

-- ten arbitrary rows, then sorted
SELECT * FROM orders
WHERE ROWNUM <= 10
ORDER BY total DESC;

-- the actual top ten
SELECT * FROM (
  SELECT * FROM orders ORDER BY total DESC
) WHERE ROWNUM <= 10;

The first version has been quietly reporting the wrong ten rows in a lot of companies for a long time.

2. An inner join inside outer joins

One JOIN in a chain of LEFT JOINs removes every row without a match. On an ERP schema this typically drops exactly the records the report exists to highlight — the ones with something missing.

3. NVL covering a question

NVL(amount, 0) is correct when a missing amount genuinely means zero. It is a bug when the amount is missing because a join failed, because it converts a data problem into a plausible-looking total.

4. Implicit date conversion

WHERE created_at > '01-JAN-26' works from one client and fails from another, because it depends on the session's NLS_DATE_FORMAT. Scheduled jobs are where this surfaces. Always TO_DATE('2026-01-01','YYYY-MM-DD') or DATE '2026-01-01'.

5. A join key that stopped being unique

If a key gains duplicates, every SUM below the join multiplies. The query did not change; the data did. Count rows at each join step and compare with a period you trust.

A safe way to work on it

Get an explanation before touching anything. Paste it into the box at the top of this page and read the walkthrough alongside the query.
Establish a baseline. Run it for a closed period and save the output. That is your regression test.
Change one thing. Re-run for the same period. Compare row counts first, totals second.
Keep the old query in a comment with the date and the reason. The next person will need it.

If it is an ERP report

Oracle E-Business Suite, SAP Business One and similar systems have schemas built for the application, not for reporting: hundreds of columns, flag columns with numeric codes, and organisation or ledger keys that must be in the join or the numbers double. Two rules save most of the pain: never join a transaction table without the organisation key, and never trust a status code without finding its lookup table.

When the query is understood and still slow, tuning it is the next step — and if it is throwing an ORA error, paste the query with the error.