Geek Answers Handbook

The request calls ORA-00904 Invalid identifier when using connections

I came across some strange behavior that prevents me from setting up the query the way I would like. Any ideas will be appreciated. If there is information about tables that would be useful, let me know. I looked through them, and nothing slipped out on me that could cause this, but I did not know what I was looking for. Here is the behavior.

This works great:

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b,
    SCHEMA_C.TABLE_C c,
    SCHEMA_A.TABLE_D d
Where 
    b.friend_id = c.friend_id
    AND a.group_id = d.group_id
    AND b.group_cd = d.group_cd

But this returns ORA-00904: b.friend_id = c.friend_id: invalid identifier

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b,
    SCHEMA_A.TABLE_D d
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Where 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd

This returns ORA-00904: b.group_cd = d.group_cd: invalid identifier

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Join 
    SCHEMA_A.TABLE_D d
On 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd

And this works again:

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id
Join 
    SCHEMA_A.TABLE_D d
On
    b.group_cd = d.group_cd
Where  
    a.group_id = d.group_id
+4
source share
3 answers

using, join

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C USING(friend_id)
Join 
    SCHEMA_A.TABLE_D d using(group_id)
where b.group_cd = d.group_cd;

, , , , invalid identifier.

: , TABLE_C TABLE_D, TABLE_B,

Select * 
From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_A.TABLE_D d,
    SCHEMA_B.TABLE_B b
Join 
    SCHEMA_C.TABLE_C c using(friend_id)
Where 
    a.group_id = d.group_id
    AND b.group_cd = d.group_cd;

, , , from, TABLE_B.

, invalid identifier, , , .

+2

, SQL, / . , , , , , .

-. . , , .

- . , , c d , . , b.friend_id , , .

b, c d . a.group_id . , .

+2

The problem is mixing compound types. When you use a comma delimited table in a sentence FROM, each term is evaluated separately. Take the following example:

From 
    SCHEMA_A.TABLE_A a,
    SCHEMA_B.TABLE_B b,
    SCHEMA_A.TABLE_D d
Join 
    SCHEMA_C.TABLE_C c
On
    b.friend_id = c.friend_id

The database is trying to resolve SCHEMA_A.TABLE_D d Join SCHEMA_C.TABLE_C c On b.friend_id = c.friend_id. It bmakes no sense in this area (this is a separate term).

For your third version, I get "A"."GROUP_ID": invalid identifierthat also matches this explanation.

Ultimately, the lesson you should learn from this is not to mix connection types.

+1
source

More articles:


All Articles