Simple ABAP query

[Former Member]

Hi,

I want to read table VBRK to see if VBELN fetched from table VBFA exists in VBRK when VBRK-FKART = 'F8'.

If it does not exist for given FKART = 'F8' in VBRK, then error else print the value.

How can I do this optimally ?

[Former Member]

something like:

REPORT ztest.

TABLES: vbfa, vbrk.

DATA: BEGIN OF vbfa_int OCCURS 0,
        vbeln LIKE vbfa-vbeln,
      END   OF vbfa_int.

SELECT vbeln FROM  vbfa
  INTO TABLE vbfa_int
  UP TO 100 ROWS.

SORT vbfa_int.
DELETE ADJACENT DUPLICATES FROM vbfa_int.
CHECK NOT vbfa_int[] IS INITIAL.

LOOP AT vbfa_int.
  SELECT fkart FROM  vbrk
    INTO vbrk-fkart
    WHERE vbeln = vbfa_int-vbeln.
    IF sy-subrc = 0.
      IF vbrk-fkart = 'F8'.
        WRITE: /001 vbfa_int-vbeln.
      ELSE.
* Error
      ENDIF.
    ELSE.
* Error
    ENDIF.
  ENDSELECT.
ENDLOOP.

Rob

[wilbertsison]

Often the simple questions are the hardest to answer.

There're are probably thousands of threads relating to optimizing internal table joins and they don't always agree.

Here is what I'd typically do.

I assume that you'd already have an internal table of VBFA entries... let's call it LT_VBFA.

types :
  begin of ty_vbeln,
    vbeln type vbeln,
  end of ty_vbeln.

field-symbols :
  <fs_vbfa>   type ty_vbeln.

data :
  lt_vbfa         type sorted table of ty_vbeln
                  with non-unique key vbeln,
  lt_vbrk         type sorted table of ty_vbeln
                  with unique key vbeln.
constants :
  co_vbtyp_inv    type vbtyp value 'M',
  co_fkart_inv    type fkart value 'F2'.


* Simulated
Select vbeln up to 100 rows
  from vbfa
  into table lt_vbfa
  where vbtyp_n = co_vbtyp_inv. "invoice

check  lt_vbfa[] is not initial.

* VBRK call
select distinct vbeln
  from vbrk
  into table lt_vbrk
  for all entries in lt_vbfa
  where vbeln = lt_vbfa-vbeln
  and   fkart = co_fkart_inv.

loop at lt_vbfa assigning <fs_vbfa>.
  read table lt_vbrk transporting no fields
    with key vbeln = <fs_vbfa>-vbeln.
  if sy-subrc ne 0.
    write : / 'ERROR: ', <fs_vbfa>-vbeln.
  else.
    write : / 'OK   : ', <fs_vbfa>-vbeln.
  endif.
endloop.

<u><b>SQL ANALYSIS</b></u>

to the previous post... yes, I confess, I've done it that way too - as sometimes it's unavoidable.

<b>LOOP/SELECT :</b>

This approach gets 1 DB hit per 1 record returned. For 100 entries you get 100 PREPARE and 100 FETCH operations

<b>SELECT/'FOR ALL ENTRIES' :</b>

I try to get less hits on the DB whenever possible.

This approach gets expanded at the DB level to show every key ot LT_VBFA. On oracle systems... several lines get bunched up in one execution (depending on the systems' max_blocking_factor ). On 100 run, on my local db... I only get 1 PREPARE operation and 20 FETCH operations.

The bad side is that this is memory intensive.But at < 10000 lines this factor is normally livable.

<b>GENERALLY : SELECT/JOIN</b>

If I had to start from scratch, a typical approach would be to use SELECT with JOIN to combine tables. Though, seeing that VBFA is one of your tables it is probably not recommended... as it is a pretty darn big one.

<b>READING VBFA</b> Reading VBFA is tricky. If it was a single read, instead of SELECT, I'd recommend using 'RV_ORDER_FLOW_INFORMATION' or some similar BAPI . RV_ORDER_FLOW_INFORMATION is unreleased but it is the common approach to retrieving document flow.

AGAIN... this is a general scenario. Depending on requirement... some other methods may be better. (Optimization can take a whole book to expand)

[Former Member]

Wilbert - when I looked at your code, I had to admit to myself that it does look more efficient than the code I posted. I put both sets of code into a program and ran them both, trapping the run time. For 100 rows, my code ran more quickly; for 1000 records yours ran more quickly.

As you said optimization is an imperfect science. In the final analysis, it's up to the programmer to test his or her code in his or her environment.

Rob