topic Re: ABAP Inner join where statement uses same field twice in Application Development and Automation Discussions

ABAP Inner join where statement uses same field twice

jeff_broodwar — Fri, 12 May 2023 04:31:41 GMT

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

Re: ABAP Inner join where statement uses same field twice

thkolz — Fri, 12 May 2023 05:39:11 GMT

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.

Re: ABAP Inner join where statement uses same field twice

Tomas_Buryanek — Fri, 12 May 2023 05:44:33 GMT

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).

Re: ABAP Inner join where statement uses same field twice

Sandra_Rossi — Fri, 12 May 2023 05:49:07 GMT

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

Re: ABAP Inner join where statement uses same field twice

jeff_broodwar — Fri, 12 May 2023 06:46:09 GMT

Noted thanks

Re: ABAP Inner join where statement uses same field twice

jeff_broodwar — Fri, 12 May 2023 06:48:14 GMT

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.

Re: ABAP Inner join where statement uses same field twice

jeff_broodwar — Fri, 12 May 2023 06:49:30 GMT

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.

Re: ABAP Inner join where statement uses same field twice

Tomas_Buryanek — Fri, 12 May 2023 07:20:48 GMT

jeff.broodwar Yes one range table would be best 🙂

Re: ABAP Inner join where statement uses same field twice

jeff_broodwar — Fri, 12 May 2023 12:04:25 GMT

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