Hi,
I seldom use FULL OUTER JOINs and today "by mistake" I wrote one that ended up in an ORA-00904 error.
Even though the join condition doesn't make much sense, I don't understand the reason for this error.
Here is a basic example for you to reproduce:
DB version : 11.2.0.3
user : SYSTEM
query:
select t1.type_name, t2.type_name
from dba_types t1
full join dba_types t2 on t1.type_name like '%TYP' and t2.type_name like '%TYP';
Connecté à :
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> show user
USER est "SYSTEM"
SQL>
SQL> select t1.type_name, t2.type_name
from dba_types t1
full join dba_types t2 on t1.type_name like '%TYP' and t2.type_name like '%TYP';
select t1.type_name, t2.type_name
*
ERREUR à la ligne 1 :
ORA-00904: "T2"."TYPE_NAME" : identificateur non valide
SQL>
If I use the USER_TYPES instead, it works:
select t1.type_name, t2.type_name
from user_types t1
full join user_types t2 on t1.type_name like '%TYP' and t2.type_name like '%TYP';
SQL> select t1.type_name, t2.type_name
from user_types t1
full join user_types t2 on t1.type_name like '%TYP' and t2.type_name like '%TYP';
TYPE_NAME TYPE_NAME
------------------------------ ------------------------------
LOGMNR$TAB_GG_REC
LOGMNR$COL_GG_REC
LOGMNR$SEQ_GG_REC
LOGMNR$KEY_GG_REC
LOGMNR$TAB_GG_RECS
LOGMNR$COL_GG_RECS
LOGMNR$SEQ_GG_RECS
LOGMNR$KEY_GG_RECS
REPCAT$_OBJECT_NULL_VECTOR
LOGMNR$COL_GG_REC
LOGMNR$COL_GG_RECS
TYPE_NAME TYPE_NAME
------------------------------ ------------------------------
LOGMNR$KEY_GG_REC
LOGMNR$KEY_GG_RECS
LOGMNR$SEQ_GG_REC
LOGMNR$SEQ_GG_RECS
LOGMNR$TAB_GG_REC
LOGMNR$TAB_GG_RECS
REPCAT$_OBJECT_NULL_VECTOR
18 ligne(s) sélectionnée(s).
SQL>
What's so special about the DBA_TYPES view that causes the full outer join to fail? Am I missing something?
Note: I haven't tried in other DB versions.
Thanks