ABAP Inner join where statement uses same field twice

[jeff_broodwar]

Hi Friends,

I have a requirement where I'll be using inner join to collect data from vbak, vbap and vbuk. Now I haven't encountered this type of requirement before, due to lack of data, can you please tell me if this is possible?

- Requirement is to join 3 tables, but I must also collect order number from a custom table and join these old order numbers to the initial internal table result of the inner join. Therefore, the result of the inner join will be from the 3 tables plus consider also the values inside the range to when checking the 3 tables..

- Please see my where statement below, where I used vbak-vbeln(a~vbeln) to check selection screen input s_vbeln and used it again to include data from a custom table that I saved in a range in the following statement vbak-vbeln in lt_err_range.

inner join vbuk as c
       on a~vbeln = c~vbeln
      where a~vbeln in s_vbeln
      and   a~vbeln in lt_err_range
      and   a~auart = p_auart
      and   a~kunnr in s_kunnr
      and   a~erdat in s_erdat
      and   a~vkorg = p_vkorg
      and   a~bsark = p_bsark
      and   c~lfstk = c_a.

I haven't tried something like this before but is this acceptable?

Appreciate your thoughts.

Thank you,

Geoff

[thkolz]

Hi Jeff,

If I understood your requirement correctly (VBELN must be only in one of the ranges), you need to use OR in your WHERE clause:

      WHERE ( a~vbeln in s_vbeln OR a~vbeln in lt_err_range )
        AND ...

You could also build one common range table for VBELN, but that's not needed.

Another thing:
There's a 1:1 relationship between VBAK and VBUK.
But I just had a look: In our system VBUK is empty.

VBAK also has the field LFSTK (Delivery Status (All Items)).
Why do you need to JOIN with VBUK at all?

You can also use

and   a~lfstk = c_a.

instead.

Best regards,
Thorsten.

[jeff_broodwar]

nope not one in the ranges using OR, but it should be searched in both selection screen and range table... which gave me an idea... I can just put all records in one range instead before putting it in inner join.

[Tomas_Buryanek]

Hello,

yes one table column (VBAK-VBELN) can have multiple WHERE conditions in one select.
In your case it is two range tables (IN operator) and they will be combined together in Database Select.

There can only be issue with contradicting conditions. For example "I EQ 123" + "E EQ 123"... But that can happen even in single range table. I am mentioning this that you should probably use s_vbeln to filter lt_err_range (or something like that, depends on your goal).

[jeff_broodwar]

Thanks for highlighting the point, I missed that. perhaps I can just put it all in one range table (eliminating duplicates) before using it in the inner join statement right? I think it's more clean and efficient that way.

[Tomas_Buryanek]

jeff.broodwar Yes one range table would be best 🙂

[Sandra_Rossi]

Remark: use ABAP Clean Code policy, i.e. name c_not_yet_processed (value 'A') rather than c_a.

[jeff_broodwar]

Noted thanks

[jeff_broodwar]

Thank you everyone for sharing your ideas, helped me come up with a solution.