4 Replies Latest reply: Mar 30, 2013 11:54 PM by 947349 RSS

    LEFT OUTER JOIN ISSUE

    947349
      HI ,

      Can you clarify join issue

      i have join like LEDGER FACT>>GENERAL>>RPT

      I am taking column from PL_LINE(RPT) AND sum(c.MONTH)

      I need all column from PL_LINE(RPT) NEED TO BE DISPLAYED,i have used left outer join,still its not displaying all columns,can i know wat mistake in below query

      select a.PL_LINE as c1,sum(c.MONTH) as c2
      from
      RPT a left outer join GENERAL b On a.FC_GL_NO = b.GL_FC_GL_NO ,
      LEDGER FACT c
      where (c.GL_ACC= b.GL_ACC)
      group by a.PL_LINE

      NOTE:TRIED RIGHT OUTER JOIN ALSO STILL NO USE
        • 1. Re: LEFT OUTER JOIN ISSUE
          Veeresh Rayan
          Use these below query

          select a.PL_LINE as c1,sum(c.MONTH) as c2
          from
          RPT a,GENERAL b ,LEDGER FACT c
          a.FC_GL_NO = b.GL_FC_GL_NO ,
          and c.GL_ACC= b.GL_ACC(+)
          group by a.PL_LINE

          OR

          select a.PL_LINE as c1,sum(c.MONTH) as c2
          from
          RPT a,GENERAL b ,LEDGER FACT c
          a.FC_GL_NO(+) = b.GL_FC_GL_NO ,
          and c.GL_ACC= b.GL_ACC
          group by a.PL_LINE

          This is not the forum to ask SQL related queries unless you are using these queries in RPD.

          Mark Hellpful or correct if helped.

          Regards,
          Veeresh Rayan
          • 2. Re: LEFT OUTER JOIN ISSUE
            947349
            Veeresh,

            OBIEE 11.1.1.5

            Thanks for your reply,yes i am using this query in OBIEE only,i tried ur query,still i am not able to see all,

            i dragged and put PL_LINE column to GENERAL table AND PUT RIGHT OUTER JOIN IN IT.(BMM LAYER)
            • 3. Re: LEFT OUTER JOIN ISSUE
              DNK
              Hi You want all *"columns"* but your selecting only two columns. Does nt make sense

              Put

              select a.PL_LINE as c1, a.FC_GL_NO, b.GL_FC_GL_NO,c.GL_ACC,b.GL_ACC ,sum(c.MONTH) as c2
              from
              RPT a left outer join GENERAL b On a.FC_GL_NO = b.GL_FC_GL_NO ,
              LEDGER FACT c
              where (c.GL_ACC= b.GL_ACC)
              group by a.PL_LINE, a.FC_GL_NO, b.GL_FC_GL_NO,c.GL_ACC,b.GL_ACC

              Mark Correct

              Thanks
              NK

              Edited by: DNK on Mar 28, 2013 6:59 AM
              • 4. Re: LEFT OUTER JOIN ISSUE
                947349
                HI,

                Its not all columns in tables,I need all values from particular column (a.PL_LINE) in RPT table,so i gav left outer join,so watever thr left table it shud display,but it did not work