2 Replies Latest reply on Mar 8, 2015 7:58 AM by ShayG

    Filter before left join

    ShayG

      I've been developing a report which needs to display a parent - child hierarchical table, each node on the table has more fields joined by left outer join in another table.

      I will try to describe the structure graphically:

      1

      --1.1

      --1.2

      -----1.2.1

      -----1.2.2

      -----1.2.3

      --1.3

      2

       

      1 for example can have multiple appearances on the second table:

      1      A    X

      1      B    Y

      1      C    Z

      1.1   B    W

      1.3   B    R

       

      The requirement is that the user selects a value from a prompt from the second table second column for example 'B'

      The resulted display should be:

      1                             B    Y

      --1.1                       B    W

      --1.2

      -----1.2.1

      -----1.2.2

      -----1.2.3

      --1.3                        B   R

      2

       

      Basically what I need to do is to filter the hierarchical table according to values from the second table while still showing the empty spaces.

      If I would do it in SQL in some software I would pass a variable and do it as follows:

       

      ================================

      SELECT T1.C1, T2.C2, T2.C3 FROM T1

      LEFT JOIN T2 ON

      T2.C1 = T1.C1 and T2.C2 =:varB 

      ================================

      varB in our case is 'B'.

       

      Until this moment I haven't found a way doing this kind of report in OBIEE because basically

      The query that the system creates is something like this:

      ================================

      SELECT T1.C1, T2.C2, T2.C3 FROM T1

      LEFT JOIN T2 ON

      T2.C1 = T1.C1

      WHERE T2.C2 =:varB 

      ================================

      And in this case of course the empty rows are also filtered out and will not be displayed.

      Will appreciate any help.

        • 1. Re: Filter before left join
          Nathan P.

          Yeah, I've ran into that before and I can't remember what I did, but I would start with checking Include NULLs in the report options. Next would be working the repository to force the behavior by leveraging the total levels of the dimensional hierarchy (I would avoid this as it would be very messy looking).

          • 2. Re: Filter before left join
            ShayG

            Hi'

            Thanks for the reply.

            Including the NULLs will result in adding all the irrelevant values from the second table, so it will not work.

            Didn't quite get the second part of your answer.