BusinessObjects Board

left outer join not working in abap data flow

dataservices sap-erp-bw
#1

We are new to ABAP data flows and are having issues getting left outer joins to work properly.

It seems like they are being processed as inner joins.

The ‘FROM CLAUSE’ section of the ‘FROM’ tab shows this:


FROM  VBRK ( SET ("table_weight" = '1') ) __SAP_LEFT_OUTER_JOIN VBRP ( SET ("table_weight" = '2') ) ON (
VBRK.VBELN = VBRP.VBELN
)
 SET("ui_on_text"='VBRK.VBELN = VBRP.VBELN')  __SAP_LEFT_OUTER_JOIN VBPA ( SET ("table_weight" = '5') ) ON (
VBRP.VBELN = VBPA.VBELN and
VBRP.POSNR = VBPA.POSNR and VBPA.PARVW = 'RE'
)
 SET("ui_on_text"='VBRP.VBELN = VBPA.VBELN and
VBRP.POSNR = VBPA.POSNR and VBPA.PARVW = \'RE\'')  __SAP_LEFT_OUTER_JOIN VBPA_1 ( SET ("table_weight" = '6') ) ON (
VBRP.VBELN = VBPA_1.VBELN and
VBRP.POSNR = VBPA_1.POSNR and VBPA_1.PARVW = 'WE'
)
 SET("ui_on_text"='VBRP.VBELN = VBPA_1.VBELN and
VBRP.POSNR = VBPA_1.POSNR and VBPA_1.PARVW = \'WE\'')  __SAP_LEFT_OUTER_JOIN VBPA_2 ( SET ("table_weight" = '7') ) ON (
VBRP.VBELN = VBPA_2.VBELN and
VBRP.POSNR = VBPA_2.POSNR and VBPA_2.PARVW = 'AF'
)
 SET("ui_on_text"='VBRP.VBELN = VBPA_2.VBELN and
VBRP.POSNR = VBPA_2.POSNR and VBPA_2.PARVW = \'AF\'')  __SAP_LEFT_OUTER_JOIN VBUK ( SET ("table_weight" = '3') ) ON (
VBRK.VBELN = VBUK.VBELN
)
 SET("ui_on_text"='VBRK.VBELN = VBUK.VBELN')  __SAP_LEFT_OUTER_JOIN VBUP ( SET ("table_weight" = '4') ) ON (

VBRP.AUPOS = VBUP.POSNR and
 VBRP.AUBEL = VBUP.VBELN
)
 SET("ui_on_text"='
VBRP.AUPOS = VBUP.POSNR and
 VBRP.AUBEL = VBUP.VBELN')

I think it is these parts that are kicking in and causing it to act as an inner join:

VBRP.VBELN = VBPA_1.VBELN and
VBRP.POSNR = VBPA_1.POSNR and VBPA_1.PARVW = ‘WE’

That works fine is normal data flows (and in SQL). Am I on the right track ?

I can re-write and pre-filter these, but I wanted to know if I am on the right track first.

Thanks.

Leigh Kennedy :australia: (BOB member since 2012-01-17)

#2

Hi Leigh,

Knowing that you are on BODS 4.x your version does support the usage of left outer and inner joins in 1 query on the abap side.

What I can see is that you have the join order of the tables incorrect. VBRP is on 7, while it is only supposed to be used in a left outer join.

By the way, what you are extracting is normally pretty heavy. If you want to use this in a daily delta I strongly suggest to extract it per table.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

#3

I don’t know what you mean doesn’t


FROM  VBRK ( SET ("table_weight" = '1') ) __SAP_LEFT_OUTER_JOIN VBRP ( SET ("table_weight" = '2') ) ON (

Mean that is is second in the join order ? which seems right to me - I join the header to the items.

As to volumes, I didn’t show the where clause which filters it meaning it isn’t too bad on performance. The reason I am not just pulling the tables on their own is they don’t all have this date logic, so I would need to pull the whole table each time otherwise.


((VBRK.AEDAT >= $EXTRACTSTARTDATE  AND VBRK.AEDAT <= $EXTRACTENDDATE ) or
  (VBRK.ERDAT >= $EXTRACTSTARTDATE AND VBRK.ERDAT <= $EXTRACTSTARTDATE ))

UPDATE:

ARGHHH !!!

Just notices the bug in my where cluase

:hb: :hb: :hb: :hb: :hb: :hb: :hb: :hb: :hb:

Update 2: This didn’t fix it. Outer joins don’t see to be working. inner joins do however…

Leigh Kennedy :australia: (BOB member since 2012-01-17)

#4

Sorry, I meant the join rank. The weight is your join rank and determines which tables are joined first.

Join can define the join rank on the tables (old, keep to 0 otherwise for backwards compatability this figure will be used) or in the From tab (new).

The higher the earlier the table will be used “first”. Same join rank means that it will be joined together.

So you set a filter on VBRK (header & starting table) to get new and changed records. That means that VBRK needs to have the highest join rank, not as I see now the lowest (1). So you switched the logic :wink:

By the way, the SAP supplement in the BODS manual has some pretty good explanations on this behaviour. On the SAP side this is extremely important to set for performance! On the database side it does not matter as BODS lets the database analyser handle the join, unless the data is processed on the job server.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

#5

Oh… your saying higher is better…

Then SAP don’t understand the meaning of the word rank as I do then… I was assuming its 1,2,3, i.e. 1st 2nd, 3rd !

Leigh Kennedy :australia: (BOB member since 2012-01-17)