Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ABAP Inner join where statement uses same field twice

jeff_broodwar
Active Participant
0 Kudos
1,358

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

1 ACCEPTED SOLUTION

Tomas_Buryanek
Active Contributor
0 Kudos
1,200

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

-- Tomas --
8 REPLIES 8

thkolz
Contributor
0 Kudos
1,200

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.

0 Kudos
1,200

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
Active Contributor
0 Kudos
1,201

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

-- Tomas --

0 Kudos
1,200

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.

0 Kudos
1,200

jeff.broodwar Yes one range table would be best 🙂

-- Tomas --

Sandra_Rossi
Active Contributor
1,200

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

jeff_broodwar
Active Participant
0 Kudos
1,200

Noted thanks

jeff_broodwar
Active Participant
0 Kudos
1,200

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