7 Replies Latest reply: Oct 11, 2011 11:28 AM by user07118719 RSS

    Outer Join Query result different

    user07118719
      Hi All,

      Below query yields result expected result


      select DECODE(FPOVR.PROFILE_OPTION_VALUE,
      null, FPOVS.PROFILE_OPTION_VALUE, FPOVR.PROFILE_OPTION_VALUE) ZZ
      from FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOVR,
      FND_PROFILE_OPTION_VALUES FPOVS where 1 = 1 and FPOVS.LEVEL_ID(+) = 10001
      and FPOVS.LEVEL_VALUE(+) = 0
      and FPOVS.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
      and FPOVR.LEVEL_ID(+) = 10003 and FPOVR.LEVEL_VALUE(+) = 21533
      and FPOVR.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
      and fpo.profile_option_name = 'GL_SET_OF_BKS_ID'


      But below query does not yield expected result.


      select DECODE(FPOVR.PROFILE_OPTION_VALUE,
      null, FPOVS.PROFILE_OPTION_VALUE, FPOVR.PROFILE_OPTION_VALUE) ZZ
      from FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOVR,
      FND_PROFILE_OPTION_VALUES FPOVS where 1 = 1 and FPOVS.LEVEL_ID(+) = 10001
      and FPOVS.LEVEL_VALUE(+) = 0
      and FPOVS.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
      and FPOVR.LEVEL_ID(+) = 10003 and FPOVR.LEVEL_VALUE = 21533
      and FPOVR.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
      and fpo.profile_option_name = 'GL_SET_OF_BKS_ID'

      Difference between them is outer join with FPOVR.LEVEL_VALUE and hard coded value. Why these above query return different results even though outer join is made with hard coded value?

      Thanks,
      SK
        • 1. Re: Outer Join Query result different
          Centinul
          SK wrote:
          Difference between them is outer join with FPOVR.LEVEL_VALUE and hard coded value. Why these above query return different results even though outer join is made with hard coded value?
          It doesn't make logical sense to outer join aganist only some of the columns in a table.
          • 2. Re: Outer Join Query result different
            Frank Kulash
            SK wrote:
            Hi All,

            Below query yields result expected result


            select DECODE(FPOVR.PROFILE_OPTION_VALUE,
            null, FPOVS.PROFILE_OPTION_VALUE, FPOVR.PROFILE_OPTION_VALUE) ZZ
            from FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOVR,
            FND_PROFILE_OPTION_VALUES FPOVS where 1 = 1 and FPOVS.LEVEL_ID(+) = 10001
            and FPOVS.LEVEL_VALUE(+) = 0
            and FPOVS.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
            and FPOVR.LEVEL_ID(+) = 10003 and FPOVR.LEVEL_VALUE(+) = 21533
            and FPOVR.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
            and fpo.profile_option_name = 'GL_SET_OF_BKS_ID'


            But below query does not yield expected result.


            select DECODE(FPOVR.PROFILE_OPTION_VALUE,
            null, FPOVS.PROFILE_OPTION_VALUE, FPOVR.PROFILE_OPTION_VALUE) ZZ
            from FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOVR,
            FND_PROFILE_OPTION_VALUES FPOVS where 1 = 1 and FPOVS.LEVEL_ID(+) = 10001
            and FPOVS.LEVEL_VALUE(+) = 0
            and FPOVS.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
            and FPOVR.LEVEL_ID(+) = 10003 and FPOVR.LEVEL_VALUE = 21533
            and FPOVR.PROFILE_OPTION_ID(+) = FPO.PROFILE_OPTION_ID
            and fpo.profile_option_name = 'GL_SET_OF_BKS_ID'

            Difference between them is outer join with FPOVR.LEVEL_VALUE and hard coded value.
            Actually, the difference is that the condition relating FPOVR.LEVEL_VALUE to 21533 is part of the outer join condition in the first query, but it is not in the second query.
            Why these above query return different results even though outer join is made with hard coded value?
            When you say
            FPOVR.LEVEL_VALUE(+) = 21533
            (with a + sign) only rows from fpovr with level_value = 21533 will be joined to the other table. If there are no such rows in fpovr, then a row of output will be generatedd for each row in the other table anyway.
            When you say
            FPOVR.LEVEL_VALUE = 21533
            (without a + sign) then the outer join is done using the other conditions, which may result in some rows from the other table appearing in the result set with NULL in places where the values from fpovr would otherwise appear. Then, after the join is finished, the condition is appled, and all those rows with NULL in place of fpovr.level_value are removed, because they do not meet the condition "FPOVR.LEVEL_VALUE = 21533". The effect is the same as an inner join.

            When using the old outer join notation (that is, with + signs), if any condition involving table fpovr has a + sign, then all conditions involving table fpovr must have a + sign, or else the result will be the same as if none of the conditions had a + sign.
            • 3. Re: Outer Join Query result different
              user07118719
              Thanks Frank kulash.


              When using the old outer join notation +(that is, with+ + signs), if any condition involving table fpovr has a + sign, then all conditions involving table fpovr must have a + sign, or else the result will be the same as if none of the conditions had a + sign.

              I was not aware of this. So you are saying second query (query without +) is equivalent to not doing any outer join with table fpovr?

              Outer join keyword RIGHT OUTER JOIN or LEFT OUTER JOIN will work in the same way as ( + ) or is it different?

              Thanks,
              SK

              Edited by: SK on Oct 11, 2011 8:40 PM

              Edited by: SK on Oct 11, 2011 8:41 PM
              • 4. Re: Outer Join Query result different
                Frank Kulash
                Hi,
                SK wrote:
                Thanks Frank kulash.


                When using the old outer join notation +(that is, with+ + signs), if any condition involving table fpovr has a + sign, then all conditions involving table fpovr must have a + sign, or else the result will be the same as if none of the conditions had a + sign.

                I was not aware of this. So you are saying second query (query without +) is equivalent to not doing any outer join with table fpovr?
                The second query is not equivalent to doing an outer join; the second query is equivalent to doing an inner join.
                The following is the correct way to do an outer join in Oracle 8 and earlier:
                SELECT     *
                FROM     fnd_profile_options          fpo
                ,     fnd_profile_option_values      fpovr
                WHERE     fpovr.level_id (+)           = 10003 
                AND      fpovr.level_value (+)          = 21533
                AND     fpovr.profile_option_id (+)      = fpo.profile_option_id
                ;
                If you leave out any of the 3 + signs, it's eqauivalent to leaving out all of them.
                Outer join keyword RIGHT OUTER JOIN or LEFT OUTER JOIN will work in the same way as ( + ) or is it different?
                An ANSI equivalent of the query above is:
                SELECT     *
                FROM           fnd_profile_options          fpo
                LEFT OUTER JOIN  fnd_profile_option_values      fpovr  ON   fpovr.profile_option_id  = fpo.profile_option_id
                                                     AND  fpovr.level_id            = 10003 
                                                              AND  fpovr.level_value           = 21533
                ;
                Notice how all 3 conditions are in the ON clause. That corresponds to the older notation, where all 3 conditions had a + sign.

                The query above is NOT equivalent to:
                SELECT     *
                FROM           fnd_profile_options          fpo
                LEFT OUTER JOIN  fnd_profile_option_values      fpovr  ON   fpovr.profile_option_id  = fpo.profile_option_id
                                                     AND  fpovr.level_id            = 10003 
                WHERE       fpovr.level_value  = 21533     -- Clearly a mistake
                ; 
                which corresponds to the 2nd query in your original message, and does not produce the results you want. The query immediately above is equivalent to an INNER JOIN.
                • 5. Re: Outer Join Query result different
                  user07118719
                  Thanks a lot for your prompt reply.

                  I have one more doubt:

                  I am working with oracle 11g. Can you tell me why you have mentioned The following is the correct way to do an outer join in Oracle 8 and earlier: . Is it different in 8i afterwords? i.e 9i,10g,11g...


                  Thanks,
                  SK
                  • 6. Re: Outer Join Query result different
                    Frank Kulash
                    Hi,
                    SK wrote:
                    Thanks a lot for your prompt reply.

                    I have one more doubt:

                    I am working with oracle 11g. Can you tell me why you have mentioned The following is the correct way to do an outer join in Oracle 8 and earlier: . Is it different in 8i afterwords? i.e 9i,10g,11g...
                    There's no version 11<b>f</b> or 11<b>h</b>, so there's no point in saying 11<b>g</b>.
                    There are versions 11<b>.1</b> and 11<b>.2</b>, and there are differences between them, but not regarding outer joins.
                    The old way of doing outer joins (with + signs) still works in Oracle 11.1 amd 11.2.

                    ANSI join syntax (including "LEFT OUTER JOIN") was introduced in Oracle 9.1. Since you're using Oracle 9 or higher, then I suggest you use ANSI syntax, but you don't have to. If you and the people who will maintain your code really prefer the old syntax, you can continue to use it.

                    If you were using Oracle 8 (or earlier) then you wouldn't have a choice; you couldn't use "LEFT OUTER JOIN". The old notation (with + signs) is the way to do outer joins in Oracle 8. There is an alternative, which involves a UNION of two inner joins and a sub-query, but it's less efficient and requuires more coding, which is why I said usign + signs was the correct way to do outer joins in Oracle 8 and earlier.
                    • 7. Re: Outer Join Query result different
                      user07118719
                      Thanks Frank.