5 Replies Latest reply on Mar 14, 2018 2:35 PM by Christian Berg

    Issue with Full Outer Join in OBIEE Physical Query

    Souvik Manna

      I am trying to build a report with multiple fact columns. The report generates two physical queries and then does a full outer join dimension columns as below -

       

      SAWITH0 D1

          FULL OUTER JOIN SAWITH1 D2

          ON SYS_OP_MAP_NONNULL(D1.c2)   = SYS_OP_MAP_NONNULL(D2.c2)

          AND SYS_OP_MAP_NONNULL(D1.c8)  = SYS_OP_MAP_NONNULL(D2.c8)

          AND D1.c4                      = D2.c4

          AND SYS_OP_MAP_NONNULL(D1.c10) = SYS_OP_MAP_NONNULL(D2.c10)

          AND D1.c12 = D2.c12

          AND SYS_OP_MAP_NONNULL(D1.c24) = SYS_OP_MAP_NONNULL(D2.c24)

          AND SYS_OP_MAP_NONNULL(D1.c23) = SYS_OP_MAP_NONNULL(D2.c23)

          AND D1.c6                      = D2.c6

          AND D1.c7                      = D2.c7

       

      Now, as you can see in the join condition "SYS_OP_MAP_NONNULL" function is not applied to all the columns and because of this fact column returns null value in the report.

       

      Can you please suggest a way to enforce this function to be applied on all the columns in the join condition whenever report does a full outer join?