BusinessObjects Universe: Defining Complex Outer Join

A complex join contains more than one condition in the join expression. Consider the following expression where one line of sales order joins with a line on invoice,

INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM

If we define a complex join in BusinessObjects Universe Design Tool, than the option to make it outer becomes disabled.

There are three possible ways to get around this problem (that I found), which are described below.

1) Create two separate joins

Create two separate joins to split the complex expression into simple ones. This will enable the ‘Outer join’ check box. While creating the query, the universe will automatically combine the two joins into one complex expression.

2) Set the Outer Join first, then specify complex expression (ANSI92 only)

Change the expression to simple temporarily; this will enable the ‘Outer join’ checkbox. Select the ‘Outer join’ and change the join expression back to the designed complex expression. Universe Designer will not only remember that ‘Outer join’ flag but also generate the right query at runtime with outer join clause.

Note: This solution only works if ANSI92 parameter is set to ‘Yes’. That is, join/on clause syntax is used for creating SQL query.

The universe will generate SQL similar to the following:

SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
ORDER_LINE RIGHT
OUTER JOIN INVOICE_LINE
ON (INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+))

3) Directly specifying the complex expression with (+) syntax

We can directly specify the complex join expression and make it outer by using (+) syntax. Off course, this will only work when ANSI92 parameter is set to ‘No’.

Generated SQL will be something like:

SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
INVOICE_LINE,
ORDER_LINE
WHERE
( INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+) )

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide