Skip to Content

How can we SELECT ... FOR ALL ENTRIES NOT in an internal table?


Let's say we have an internal table containing composite keys for transparent table. How can we select the records from that transparent table whose keys are NOT in the internal table?

Concrete example -

Transparent table VBAP (Sales Document: Item Data) has a composite key VBELN (Sales Document number) and POSNR (Sales Document Item number). We create an internal table IVBAP_KEYS with these two columns (VBAP-VBELN, VBAP-POSNR) and populate it with a large number of entries.

This statement -

SELECT COUNT(*) INTO numrecs FROM vbap
WHERE NOT ( vbeln = ivbap_keys-vbeln AND posnr = ivbap_keys-posnr ).

assigns the total number of records in VBAP to numrecs. In other words, it is identical to -

SELECT COUNT(*) INTO numrecs FROM vbap.

How can we write an SQL statement that selects VBAP records EXCLUDING those in IVBAP_KEYS?

(My current work-around is to select all records from VBAP into an internal table, then loop over it discarding records whose keys exist in IVBAP_KEYS. It works but is painfully slow)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jun 01, 2017 at 09:28 AM

    The only suggestion I can make is SELECT ... WHERE NOT IN( ... ).

    FOR ALL ENTRIES gets converted to either an IN clause or multiple OR clauses at the DB level anyway, so your performance would be similar.

    The problem comes in when your statement exceeds the maximum size of an SQL statement, e.g. 64k (db-specific). A very large FAE clause will actually be split into several SELECT queries at the DB, which is fine if you're combining all the results.

    However, splitting a NOT IN query over several SELECT statements won't work because each query will contain records excluded by others. I suspect this may be precisely the reason why FOR ALL ENTRIES can't use NOT.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 01, 2017 at 09:51 AM

    Did you try to build a Range-table instead? You can just set all the search results of the first select as 'Excluding' in the Range table.

    Add comment
    10|10000 characters needed characters exceeded

    • Do you mean SELECT .. WHERE f1 IN some_range_table? I normally use IN for filtering single columns. How could I use SELECT ... WHERE NOT IN( ... ) for filtering a composite key?

  • Jun 01, 2017 at 08:28 AM

    How was ivbap_keys built, if it was from a SELECT query then you could use some subquery in a SELECT ... FROM VBAP WHERE NOT EXISTS( first select AND vbeln EQ vbap~vbelnd AND posnr EQ vbap~posnr ) statement.

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 01, 2017 at 02:59 PM

    How about you count all reacords and substract number of records you are looking at.

    Your select is the worst case scenario for any DB...

    Add comment
    10|10000 characters needed characters exceeded