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: 

Inner join searches too many entries

suzy_bijnens
Active Participant
0 Kudos

Hi experts,

In a user exit following code is used:

DATA : h_rsnum LIKE afko-rsnum,

h_charg LIKE resb-charg,

h_matnr LIKE resb-matnr,

SELECT SINGLE rsnum INTO h_rsnum

FROM afko

WHERE aufnr = x_bncom-aufnr.

IF sy-subrc = 0 .

SELECT resbcharg resbmatnr

INTO (h_charg, h_matnr)

FROM mara INNER JOIN resb

ON maramatnr = resbmatnr

WHERE resb~rsnum = h_rsnum

AND resb~charg NE space

AND maramtart = 'HALB' OR maramtart = 'FERT'.

ENDSELECT.

Goal of the source code is to detemine the reservation number of a process order. From table RESB with key h_rsnum, the material nb and the batch nb of the single material with material type HALB or FERT needs to be selected.

During debugging I found out that this code searches into a very long list of materials before ending up with the correct h_charg and h_matnr.

If the condition resb~rsnum = h_rsnum would be taken into account when creating the inner join, the list would only contain 20 materials maximum.

Can this behaviour be changed and how to do it?

Rgds,

Suzy

1 ACCEPTED SOLUTION

former_member182566
Active Contributor
0 Kudos

Ah, I just noticed that you will want, of course, to use parenthesis for the OR:

....

AND ( maramtart = 'HALB' OR maramtart = 'FERT' ).

7 REPLIES 7

Former Member
0 Kudos

if this is inside a user exit 1st of all why are you using a select endselect and i dont see you take that in to a table which means your only looking for one record.

about you tell that before the inner join taking the where condition into consideration. I dont think it will be possible sine normally after making the join it should go for the where condition anyway ill try to check on it and see.

sometimes Siegfried might know an answer dont know if he saw this.

Nafran

Edited by: Nafran Shiraz on Jun 4, 2009 12:36 PM

0 Kudos

Thanks for looking into this.

Yes, I'm actually looking for 1 record and my concern is that too many records are being read in order to find this 1 record.

Instead of searching through more or less 20 records (if the WHERE statement is used), it searches through 1000 or more records (haven't count them).

Rgds,

Suzy

0 Kudos

Hi Suzy,

To confirm what you should expect:

Go to SE16, RESB, and filter by RSNUM (and charg <> ' ').

How many materials are there?

You can believe that WHERE clauses and INNER JOINs do work, of course, so you must check if your assumptions are correct. The problem surely is not that INNER JOIN is not joining...

Rui Dantas

former_member182566
Active Contributor
0 Kudos

Ah, I just noticed that you will want, of course, to use parenthesis for the OR:

....

AND ( maramtart = 'HALB' OR maramtart = 'FERT' ).

0 Kudos

Hi,

If I put parenthesis, the program check states : the field "maramtart = 'HALB' OR maramtart = 'FERT'" is unknown.

I want to go to SE16 and filter on rsnum. This would be a list of maximum 20 materials. In that list I want to select all records for which the batch field is not empty and material type is HALB or FERT . There is only 1 material that will result from these search criteria.

The final result from the inner join is correct, I'm only concerned about the way the system comes to this result (system performance).

Rgds,

Suzy

0 Kudos

hi suzy,

don't forget the spaces.

SELECT resb~charg resb~matnr
INTO (h_charg, h_matnr)
FROM mara INNER JOIN resb
ON mara~matnr = resb~matnr
WHERE resb~rsnum = h_rsnum
AND resb~charg NE space
AND ( mara~mtart = 'HALB' OR mara~mtart = 'FERT' ).

Edited by: Rui Pedro Dantas on Jun 4, 2009 5:37 PM

0 Kudos

Hi,

Yes, I did forget. You solved my problem.

Thank you!

Suzy