Oracle APEX - Interactive Report ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
I recently was working on an interactive report in Oracle Apex 4 and ran across this error
The report query needs a unique key to identify each row. The supplied key cannot be used for this query. Please edit the report attributes to define a unique key column. Report ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Strange, because the query runs fine in SQL Developer and TOAD. Fortunately the query was similar to another IR report and I had only made one small change, which was to add an outer join for a status code that sometimes may be NULL.
Removing that and replacing it with an inline lookup immediately fixed the issue.
SELECT COl1, COL2, STATUS, COL4 FROM TABLE1 A, STATUS_TABLE B WHERE A.STATUS_ID=B.ID (+);
flipped around like this fixes the issue and allows APEX to use the query in an Interactive Report.
SELECT COl1, COL2, NVL((SELECT B.STATUS_NAME STATUS FROM STATUS_TABLE B WHERE A.STATUS_ID=B.ID), 'NO STATUS') STATUS, COL4 FROM TABLE1 A;