Home > oracle > ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

One of our developers has problems with a select of different tables:


SELECT ROWID,
 TMP_TABLE_ALIAS.*
FROM
 (select a.*,
 B.*,
 (SELECT MAX(TMP_CODEMP)
 FROM ATE_TABLE
 WHERE TMP_IDTRE = TRE_ID
 and TMP_FI is null
 ) AS TRE_CODEMP,
 (SELECT MAX(REV_DATA) FROM AR_TABLE WHERE REV_ID = TRE_ID
 ) AS TRE_DATREV
 FROM AT_TABLE a,
 AP_TABLE B
 WHERE PER_ID = TRE_ID (+)
 ) TMP_TABLE_ALIAS;

This statement works on the development environment, but in the production environment it fails with:


ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 01445

The database versions is the same in both environments: Oracle 10g Release 2 (10.2.0.5).

After reviewing the tables in the production environment, I noticed that one of the tables has no primary key, and that caused the ORA-01445. Even with a unique index the select fails, it’s mandatory to declare primary keys for the tables when they are involved in joins.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: