5 Replies Latest reply: Jan 14, 2013 6:57 AM by BluShadow RSS

    Outer join with BETWEEN clause

    AceNovice
      Hi All,

      I have 2 tables (A and B) which i need to join. I need all records from table A and matching records from the table B. below is the structure.

      TABLE A (total rows = 10)
      ------------
      ROW_WID
      GL_DATE
      LOCATION_CODE

      TABLE B (total = 7)
      -----------
      ROW_WID
      START_DATE
      END_DATE
      LOCATION_CODE


      Initially, we were asked to join based on location_code. In the table B, some of the LOCATION_CODE are missing, which is present in table A.

      We wrote below query

      SELECT A.*, B.START_DATE, B.END_DATE
      FROM A, B
      WHERE A.LOCATION_CODE = B.LOCATION_CODE (+)

      This gives 10 records, where 3 records have START_DATE and END_DATE NULL. because of outer join

      It gave all records from the A table. It worked fine. Now i need to add one more condition where A.GL_DATE between B.START_DATE and B.END_DATE

      If i write this

      SELECT A.*, B.START_DATE, B.END_DATE
      FROM A, B
      WHERE A.LOCATION_CODE = B.LOCATION_CODE (+)
      AND A.GL_DATE BETWEEN B.START_DATE and B.END_DATE

      This gives me only 7 records. IS IT POSSIBLE TO ADD OUTER JOIN with BETWEEN clause.
        • 1. Re: Outer join with BETWEEN clause
          jeneesh
          Is this not working?
          SELECT A.*, B.START_DATE, B.END_DATE
          FROM A, B
          WHERE A.LOCATION_CODE = B.LOCATION_CODE (+)
          AND A.GL_DATE BETWEEN B.START_DATE(+) and B.END_DATE(+)
          • 2. Re: Outer join with BETWEEN clause
            AceNovice
            Ok, I was able to resolve the issue.
            • 3. Re: Outer join with BETWEEN clause
              984670
              Hi All,

              I'm in a similar situation,

              I have a complex query, everything is working fine, except this part (I will simplify everything, removing other stuffs because I need help only on the following condition)


              SELECT *
              from transaction t, card c
              where c.card_id = t.card_id (+)
              and t.trn_date between to_date ('01/01/2012','dd/mm/yy') and ('01/01/2013,'dd/mm/yy')

              How could i OUTER JOIN the between condition?

              I tried with
              and t.trn_date between to_date ('01/01/2012','dd/mm/yy') (+) and ('01/01/2013','dd/mm/yy') (+)

              but returns a "missing expression" error.
              Surely I'm missing some stupid thing about it, could you help me on this? (probably because I'm talking about parameters and I cannot put the outer join on a value)
              How can I rewrite the condition in order to satisfy what I'm trying to accomplish?
              Thanks in Advance,
              Alex

              Edited by: 981667 on 14-gen-2013 4.23

              Edited by: 981667 on 14-gen-2013 4.24
              • 4. Re: Outer join with BETWEEN clause
                BrendanP
                Just put the OJ symbol on the lhs column, not on the rhs constants
                • 5. Re: Outer join with BETWEEN clause
                  BluShadow
                  Or just use ANSI syntax...
                  SELECT *
                  from   card c
                         left outer join transaction t
                         on (c.card_id = t.card_id
                         and t.trn_date between to_date('01/01/2012','dd/mm/yy') and ('01/01/2013,'dd/mm/yy')
                         )