3 Replies Latest reply: Jul 29, 2013 6:31 AM by chris227 RSS

    Outer join not working when comparing trimmed columns

    rahulras

      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

          Try placing the operator inside TRIM

          trim(a.col1(+))

          • 2. Re: Outer join not working when comparing trimmed columns
            ramoradba

            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

              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.