Hi All,
Oracle v11.1 Standard edition.
I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.
Is there any way, I can use old syntax and TRIM and get the outer join to work?
I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.
SQL>
SQL> desc taba
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(20)
SQL> desc tabb
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(20)
SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selected
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A A
C C
D D
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A A
C C
D D
B
SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A A
C C
D D
E
Thanks in advance