topic Re: Select-option in select query condition in Application Development and Automation Discussions

Select-option in select query condition

former_member657283 — Sat, 03 Oct 2020 20:52:40 GMT

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

Re: Select-option in select query condition

michael_piesche — Sun, 04 Oct 2020 05:30:42 GMT

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

Re: Select-option in select query condition

Sandra_Rossi — Sun, 04 Oct 2020 07:39:56 GMT

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?

Re: Select-option in select query condition

former_member1716 — Sun, 04 Oct 2020 15:54:41 GMT

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!

Re: Select-option in select query condition

RaymondGiuseppi — Mon, 05 Oct 2020 07:01:02 GMT

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.

Re: Select-option in select query condition

MateuszAdamus — Mon, 05 Oct 2020 07:21:16 GMT

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,

Mateusz

Re: Select-option in select query condition

former_member657283 — Mon, 05 Oct 2020 18:48:17 GMT

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.

Re: Select-option in select query condition

Chintu6august — Tue, 06 Oct 2020 06:50:20 GMT

Hi,

make user of FILTER keyword!!

thanks

Re: Select-option in select query condition

Sandra_Rossi — Tue, 06 Oct 2020 06:54:59 GMT

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.

Re: Select-option in select query condition

Chintu6august — Tue, 06 Oct 2020 06:58:59 GMT

Hi,

make use of EXCEPT with FILTER keyword.

Re: Select-option in select query condition

michael_piesche — Tue, 06 Oct 2020 08:55:51 GMT

rajesh_koothrappali, you have asked for an 'elegant' solution without comparing table contents.

But the 'comparison' has to be done somewhere.
So either on database level or an ABAP level.
If a computation can be done on database level, it will in sum in almost all cases outperform the same computation on ABAP level.
It also leads in a lot of cases to less data being moved between database and ABAP, less necessary database accesses as well as less memory necessary in ABAP.
Of course there will be more computation and memory necessary on the database, but it is closer to the source of the data and it has to be performed somewhere.

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?

If you dont really need it, I would definitly not do the check for matnr without hits, neither on database nor on ABAP level (thats why I questioned the resulting 'performance' (loss) of a logic that also checks for missing entries)
Just for testing I would rather add a second logic that after successfull tests can easily be deactivated or deleted before going to production, without changing the logic of the main relevant coding.