ORA-00904 when joinging two tables from two different schemas.

Toad for Oracle
1

Hi I created a table with two columns in my schema. I have select insert update delete privs.

I can update the new table in my schema. I can update a table in the main schema.

When I run a statement to update the table in the main schema using this new table, I get ORA-00904: Invalid Identifier . All tables in the update statement are identified with schema.

what could be causing this? Is this a permissions issue with crossing schemas? I can update tables in both schemas on their own, but I cant even select from the tables using a join.

FYI UTC_NUMBER is a char(10 Byte) in both tables.

Thanks in advance,

Sandy

/* I can update the new table in my schema*/
– UTC # is
select * from ITEM_TAG_UPDATE2 where UTC_NUMBER = ‘0000289978’ – X_REF is 1
update ITEM_TAG_UPDATE2 set X_REF = ‘14’ where UTC_NUMBER = ‘0000289978’
select * from ITEM_TAG_UPDATE2 where UTC_NUMBER = ‘0000289978’ – X_REF is now 14

/* I can update the table in the main schema*/
select * from TIDTRMST where UTC_NUMBER = ‘0000289978’ – TRACE_NBR_1 is 12
update TIDTRMST set TRACE_NBR_1 = ‘14’ where UTC_NUMBER = ‘0000289978’
select * from TIDTRMST where UTC_NUMBER = ‘0000289978’ – TRACE_NBR_1 is now 14

/But when I try to join the table in the main schema using the table in my schema I get ORA-00904 Invalid Identifier/

select * from INDUS.TIDTRMST
where INDUS.TIDTRMST.UTC_NUMBER = D200960.ITEM_TAG_UPDATE2.UTC_NUMBER
and INDUS.TIDTRMST.UTC_NUMBER = ‘0000289978’ – this return ORA-00904: “D200960”.“ITEM_TAG_UPDATE2”.“UTC_NUMBER”: invalid identifier

2

You can’t add table references to the WHERE clause without also adding them to the FROM clause. I guess D200960 is one of the schemas. if so, try this:

select *

from INDUS.TIDTRMST, D200960.ITEM_TAG_UPDATE2

where INDUS.TIDTRMST.UTC_NUMBER = D200960.ITEM_TAG_UPDATE2.UTC_NUMBER

and INDUS.TIDTRMST.UTC_NUMBER = ‘0000289978’

Note in this case, select * will return all columns from both tables.

3

That worked, thanks!

’