topic Re: Simple ABAP query in Application Development and Automation Discussions

Simple ABAP query

Former Member — Tue, 14 Feb 2006 22:34:00 GMT

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 ?

Re: Simple ABAP query

Former Member — Tue, 14 Feb 2006 22:55:45 GMT

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

Re: Simple ABAP query

wilbertsison — Wed, 15 Feb 2006 00:17:24 GMT

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.

SQL ANALYSIS

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

LOOP/SELECT :

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

SELECT/'FOR ALL ENTRIES' :

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.

GENERALLY : SELECT/JOIN

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.

READING VBFA 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)

Re: Simple ABAP query

Former Member — Wed, 15 Feb 2006 04:25:26 GMT

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