I have problem with following query Q1:
SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS
INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID
When we translate this to good old notation, Q2:
SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS
,MYSALES.MYCOUNTRY MCOUNTRY
,MYGLOBAL.COUNTRY COUNTRY
WHERE
MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
AND COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID
The problems start when I wrap in view:
SELECT * FROM (Q1)
or SELECT * FROM (Q2)
results with error:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 01445. 00000 - "cannot select ROWID from, or sample, a join view without a key-preserved table"
But for query Q3:
SELECT * FROM MYSALES.MYCOUNTRY MCOUNTRY
INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID
Result of SELECT * FROM (Q3)
is correct.
So the problem is in Q4:
SELECT * FROM MYSALES.MYADDRESS ADDRESS
INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
When I wrap the last query in a view, it fails, with the above error.
The columns that end with _ID
, are should be primary or references key.
Could you please guide me a hint where should I look for solution ?