10-03-2020 9:52 PM
Dears,
Is there any elegant way to know which entries in select-option have not fetched records as where condition in Select statement?
I mean without comparing tables contents. if possible.
For example which matnr in so_matnr have not fetch records:
SELECT a~infrn a~matnr a~lifnr b~ekorg
INTO TABLE itab
FROM eina AS a
INNER JOIN eine AS b on a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg.
thanks, Raj
10-05-2020 8:21 AM
Hello rajesh_koothrappali
It's not possible without a comparison. Furthermore, the SELECT-OPTIONS may contain range records, which makes any simple record by record comparison invalid.
What you could do is:
1. SELECT all materials from MARA table, to have a list of them.
SELECT matnr
FROM MARA
WHERE matnr IN s_matnr
INTO TABLE @DATA(lt_materials).
2. SELECT data from documents (based on your SQL).
SELECT a~infrn a~matnr a~lifnr b~ekorg
INTO TABLE itab
FROM eina AS a
INNER JOIN eine AS b on a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg.
3. Compare the selected data.
LOOP AT lt_materials REFERENCE INTO DATA(ld_material).
READ TABLE itab TRANSPORTING NO FIELDS
WITH KEY matnr = ld_material->matnr.
IF sy-subrc = 0.
" record exists
ELSE.
" record does not exist
ENDIF.
ENDLOOP.
Kind regards,
Mateusz10-04-2020 6:30 AM
Elegant = less coding vs. Performance (?)
If SO_MATNR comes from a selection where users can make all available select-options (eg. Includes/Excludes, Equal, Unequal, Between, Pattern, ...), than you would have to use MARA as the FROM table to select from, in order to define the relevant MATNRs and then do outer joins with EINE and EINA (available with ABAP 740, but not with 73x)
SELECT m~matnr, a~infnr, a~lifnr, b~ekorg
INTO TABLE @DATA(itab)
FROM mara AS m
LEFT JOIN eina AS a ON a~matnr = m~matnr AND a~lifnr = @p_lifnr
LEFT JOIN eine AS b ON b~infnr = a~infnr AND b~ekorg = @p_ekorg
WHERE m~matnr IN @so_matnr.
If SO_MATNR only contains Include Equal values where LOW is a relevant MATNR, or if you already have an internal table with the only relevant MATNRs, you can also join your internal table with eina and eine, instead of joining it with MARA (SO_MATNR could only be used with value LOW, if SO_MATNR is an internal table without a header structure). Be aware, that these select statement options are not possible with older ABAP versions (e.g. not available with 740, but available with 752)
TYPES: BEGIN OF ty_matnr,
matnr TYPE matnr,
END OF ty_matnr.
DATA lt_matnr TYPE SORTED TABLE OF ty_matnr WITH UNIQUE KEY matnr. " internal table containing relevant MATNRs
"SELECT matnr FROM mara INTO TABLE lt_matnr. " just for testing purposes
SELECT m~matnr, a~infnr, a~lifnr, b~ekorg
FROM @lt_matnr AS m
LEFT JOIN eina AS a ON a~matnr = m~matnr AND a~lifnr = @p_lifnr
LEFT JOIN eine AS b ON b~infnr = a~infnr AND b~ekorg = @p_ekorg
INTO TABLE @DATA(itab).
10-06-2020 9:55 AM
rajesh_koothrappali, you have asked for an 'elegant' solution without comparing table contents.
So, if you can solve your problem with one select statement, that does the same as two select statements as well as loop search (which should be optimized by accessing the second table with a primary/secondary key), I would most likely go for the one select statement. If you need the information about what materials led to a hit and which didint, you can still simply separate your resulting table in two tables with a simple loop and check whether the values other than matnr are empty or not.
PS: Do you really need to know in your programme which matnr did not lead to hits, or is it just nice to know in order to test your programme?
10-04-2020 8:39 AM
You say "which entries in select-option have not fetched records". But in select-option you may have conditions like I CP *ER*, E CP *S*, so for lines which imply material "ERS", what is the meaning of returning these 2 entries? That makes it impossible to answer your question. So I guess the question is more "which matnr values have not fetched records", right?
10-04-2020 4:54 PM
rajesh_koothrappali,
Its impossible without comparing the entries of the table.
Few simple ways would be:
-> Pass Your resultant table to a temporary internal table. Now sort the temporary table based on MATNR and delete Adjacent duplicates. With this you will have unique entries of MATNR, Now compare the no of entries in the Temporary table against Selection Screen entries. If it is equals then all values have been fetched, if there is a difference then you can compare and find the left out materials.
-> Read your resultant table against all the entries fed in the selection screen, if the read fails then that is the MATNR you are looking for.
Regards!
10-05-2020 8:01 AM
You could loop at the select-options records and for each criteria (where SIGN = 'I' include) create a range with this unique record and loop into the resulting itab for a record matching it.
10-05-2020 8:21 AM
Hello rajesh_koothrappali
It's not possible without a comparison. Furthermore, the SELECT-OPTIONS may contain range records, which makes any simple record by record comparison invalid.
What you could do is:
1. SELECT all materials from MARA table, to have a list of them.
SELECT matnr
FROM MARA
WHERE matnr IN s_matnr
INTO TABLE @DATA(lt_materials).
2. SELECT data from documents (based on your SQL).
SELECT a~infrn a~matnr a~lifnr b~ekorg
INTO TABLE itab
FROM eina AS a
INNER JOIN eine AS b on a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg.
3. Compare the selected data.
LOOP AT lt_materials REFERENCE INTO DATA(ld_material).
READ TABLE itab TRANSPORTING NO FIELDS
WITH KEY matnr = ld_material->matnr.
IF sy-subrc = 0.
" record exists
ELSE.
" record does not exist
ENDIF.
ENDLOOP.
Kind regards,
Mateusz10-05-2020 7:48 PM
Right Sandra, I meant a short coding to retrieve material values, included in selection, which get no records from Select.
Apologize for ambiguity and really thanks to everybody who gave their idea.
10-06-2020 7:50 AM
10-06-2020 7:54 AM
As I said, I guess the question is more "which matnr values have not fetched records".
It's possible to do an additional SELECT to achieve your goal, but it's better to do only one global SELECT (and then separate the two cases in ABAP, as explained by Michael).
Solution with one additional SELECT:
DATA itab_matnr TYPE TABLE OF mara-matnr.
SELECT matnr
INTO TABLE itab_matnr
FROM eina
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND NOT EXISTS(
SELECT *
FROM eina AS a
INNER JOIN eine AS b ON a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg ).
(to be compared to your original query, for reference:)
SELECT a~infrn a~matnr a~lifnr b~ekorg
INTO TABLE itab
FROM eina AS a
INNER JOIN eine AS b on a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg.
If you want only one SELECT, Michael explained how to do it with a left outer join, but you may also desire to have MATNR that exist only in EINE, without extending to MARA. In that case, it's even more simple:
SELECT a~infrn a~matnr a~lifnr b~ekorg
INTO TABLE itab
FROM eina AS a
LEFT JOIN eine AS b on a~infnr = b~infnr
WHERE a~matnr IN so_matnr
AND a~lifnr = p_lifnr
AND b~ekorg = p_ekorg.
LOOP AT itab INTO DATA(line) WHERE ekorg IS INITIAL.
" all lines from EINA whose "MATNR values have not fetched records" (no line in EINE)
ENDLOOP.
10-06-2020 7:58 AM