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

[former_member331218]

Hi,

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
FOR ALL ENTRIES IN ivbap_keys
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)

[raymond_giuseppi]

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.

[former_member331218]

IVBAP_KEYS is not populated from a single select SELECT statement. If it was, then I would build a compound SELECT statement as you suggested.

[pokrakam]

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.

[former_member331218]

I normally use IN for filtering single columns. How could I use SELECT ... WHERE NOT IN( ... ) for filtering a composite key?

[pokrakam]

You're right, I wasn't paying attention. So you will have to build up a dynamic query, similar to what a FAE clause gets converted to. This probably won't help with VBAP, but out of interest on how this could be tackled, the basic logic:

data sql_cond type string value `( `
loop at vbap_exclusions into data(exclude). 
  if sy-tabix = 1. 
    sql_cond = sql_cond && `( vbelnr <> exclude-vbelnr AND posnr <> exclude-posnr )`.
  else.
    sql_cond = sql_cond && ` AND ( vbelnr <> exclude-vbelnr AND posnr <> exclude-posnr )`.
  endif.
  if length(sql_cond) > max_sql_size.  "64k or whatever
    "handle situation where we can't do full condition
  endif.
endloop.
sql_cond = sql_cond && ` )`.

SELECT COUNT(*) INTO numrecs FROM vbap WHERE (sql_cond). 

(quickly written, might have small errors and doesn't check special cases of one or no entries)

Whether this performs better or worse than your logic really depends on the size of the table and the number of exclusions.

I suspect it won't help much as VBAP is pretty big and to get enough of an exclusion to be of performance benefit versus reading the whole table will most probably exceed the SQL statement limit.

[former_member331218]

thanks, your comment is the best answer

[Former Member]

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.

[former_member331218]

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?

[k_gorin]

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