0

I want to fetch those records from transparent table that do not exist in the FOR ALL ENTRIES itab.

Whereas default logic is to include those entries which exist in the internal table, I want to exclude them. I want some type of FOR ALL ENTRIES NOT IN statement.

Is there any workaround?

Suncatcher
  • 10,355
  • 10
  • 52
  • 90
  • The answer depends on the amount of entries that need to be excluded, are we talking tens, hundreds, thousands, ten thousands, more ? – tomdemuyt Jun 13 '12 at 14:40
  • I think there are (ten) thousands entries. – Suncatcher Jun 13 '12 at 14:47
  • Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! –  Dec 11 '15 at 11:54

3 Answers3

3

Well, after 8 years of idle I can propose possible solution for my problem.

Since release 7.52 ABAP allows putting itab as data source for SELECT statement, so the above task can be simplified to appending NOT EXISTS subquery with FOR ALL ENTRIES itab as data source:

Sample coding:

* filling FOR ALL ENTRIES table
SELECT *
  FROM spfli
  INTO TABLE @DATA(lt_exclude_FAE)
 WHERE carrid = s~carrid AND
       connid = s~connid AND
     cityfrom = 'NEW YORK'

* excluding FAE rows
SELECT *
   FROM sflight AS s
   WHERE seatsocc < s~seatsmax AND
     NOT EXISTS ( SELECT  *
                    FROM @lt_exclude_FAE AS exclude
                   WHERE carrid = s~carrid AND
                         connid = s~connid AND
                       cityfrom = s~cityfrom )
   INTO TABLE @DATA(flights_wo_ny).

Though, now this works surely only on HANA database, and maybe on couple of others.

Suncatcher
  • 10,355
  • 10
  • 52
  • 90
  • With the restriction that "This statement cannot be executed on all database systems". HANA allows this feature. – Sandra Rossi Jan 30 '20 at 16:33
  • Thx, Sandra, corrected the answer. I don't have 7.52 now so cannot test, it was only a rough guess. Can you please link to some documentation? It's interesting to read the explanation – Suncatcher Jan 31 '20 at 08:51
  • 1
    The text I mention comes from the documentation you linked (and there's more info [here](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm?file=abapselect_itab.htm)). I have 7.52 and I could test it on HANA 2.0 successfully, maybe other databases support it, but it fails at least with Sybase ASE 16.0. Note that you may test if the database has the feature: `IF cl_abap_dbfeatures=>use_features( EXPORTING requested_features = VALUE #( ( cl_abap_dbfeatures=>itabs_in_from_clause ) ) )`. – Sandra Rossi Jan 31 '20 at 10:01
  • Thanks, will test on other databases as I have chance – Suncatcher Jan 31 '20 at 10:34
1

I don't think if it is possible. I would use ranges for that. If this is not suitable, loop+read table can be used.

mars
  • 11
  • 1
0

You can use simple Query for this issue.

Check this coding... 

TABLES : table1 , table2 .

DATA : it_table1 TYPE STANDARD TABLE OF table1 ,
   it_table2 TYPE STANDARD TABLE OF table2 ,
   wa_table1 TYPE table1 ,
   wa_table2 TYPE table2 .


 SELECT * FROM table1 INTO CORRESPONDING FIELDS OF TABLE it_table1 .


 LOOP AT it_table1 INTO wa_table1 .

        SELECT field1 FROM table2 INTO CORRESPONDING FIELDS OF TABLE it_table2 WHERE field2 = wa_table1-field1 .

    IF  sy-subrc = 0 .
        delete TABLE it_table1 FROM wa_table1 .
    ENDIF.

ENDLOOP.
Dhivya
  • 518
  • 2
  • 8
  • 17