This discussion is archived
3 Replies Latest reply: Jul 29, 2013 4:31 AM by chris227 RSS

Outer join not working when comparing trimmed columns

rahulras Explorer
Currently Being Moderated

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

  • 1. Re: Outer join not working when comparing trimmed columns
    michaelrozar17 Pro
    Currently Being Moderated

    Try placing the operator inside TRIM

    trim(a.col1(+))

  • 2. Re: Outer join not working when comparing trimmed columns
    ramoradba Explorer
    Currently Being Moderated

    SQL> select * from a;

     

     

    COL1

    ----------

    a

    b

    c

    d

     

     

    SQL> select * from b;

     

     

    COL2

    ----------

    a

    b

    c

    d

     

     

    SQL> select a.col1, b.col1 from  a, b where a.col1 = b.col2 ;

    select a.col1, b.col1 from  a, b where a.col1 = b.col2

                   *

    ERROR at line 1:

    ORA-00904: "B"."COL1": invalid identifier

     

     

     

     

    SQL> select a.col1, b.col2 from  a, b where a.col1 = b.col2 ;

     

     

    COL1       COL2

    ---------- ----------

    a          a

    b          b

    c          c

     

     

    SQL> select a.col1, b.col2 from  a, b where trim(a.col1) = trim(b.col2) ;

     

     

    COL1       COL2

    ---------- ----------

    a          a

    b          b

    c          c

    d          d

     

     

    SQL> select a.col1, b.col2 from  a, b where trim(a.col1(+)) = trim(b.col2);

     

     

    COL1       COL2

    ---------- ----------

    a          a

    b          b

    c          c

    d          d

     

     

    SQL> select a.col1, b.col2 from  a, b where trim(a.col1(+)) = trim(b.col2(+));

    select a.col1, b.col2 from  a, b where trim(a.col1(+)) = trim(b.col2(+))

                                                           *

    ERROR at line 1:

    ORA-01468: a predicate may reference only one outer-joined table

     

     

     

     

    SQL> select a.col1, b.col2 from  a, b where trim(a.col1) = trim(b.col2(+));

     

     

    COL1       COL2

    ---------- ----------

    a          a

    b          b

    c          c

    d          d

     

     

    SQL>

  • 3. Re: Outer join not working when comparing trimmed columns
    chris227 Guru
    Currently Being Moderated

    ramoradba wrote:

     

     

    SQL> select a.col1, b.col2 from  a, b where trim(a.col1(+)) = trim(b.col2(+));

    select a.col1, b.col2 from  a, b where trim(a.col1(+)) = trim(b.col2(+))

                                                           *

    ERROR at line 1:

    ORA-01468: a predicate may reference only one outer-joined table

    For a full outer join you have to workaround with ansii syntax in 11.x

    From 12g on this restriction wil be fallen away.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points