Issue with Full Outer Join in OBIEE Physical Query

souvik88_570

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?

[Deleted User]

That's an in-memory stitch join which means you're doing cross-star queries which the engine has to run this way because it's what it is instructed to do by your model.

Long story short: this is due to how your RPD is built.

souvik88_570

Thanks Christian for your response.

I am okay with the way the cross-star join is happening and it is difficult at the moment to make any change in the model. My only question is if there is a way to enforce SYS_OP_MAP_NONNULL function to be applied on all the columns used as join conditions?

[Deleted User]

Enforce it all over the place? Not really. Those joins are the joins to conformed dimensions. To force those as outer joins you have to change the joins to the conformed dimensions into outer joins in your model.

You're not stating the version you're working on - this should always be the very first thing you specify.

Disabling SYS_OP_MAP_NONNULL can be done by unsetting PREFERS_NATIVE_NULLS_EQUAL_COMPARISON but it is not possible the other way around since it is model-driven as I said.

souvik88_570

No, not all over the place but for in all the conformed dimensions used in the full outer join.

I think this function is applied to those columns for which Nullable is set to True in the physical column property. I just changed this property for on e of the column where this function was not applied and it resolved the issue.

[Deleted User]

Souvik Manna wrote:No, not all over the place but for in all the conformed dimensions used in the full outer join.I think this function is applied to those columns for which Nullable is set to True in the physical column property. I just changed this property for on e of the column where this function was not applied and it resolved the issue.

As I said initially and again just before: It is in your model. Since you then claimed "is difficult at the moment to make any change" I kept trying to explain the why and how.