CODEWITHSUNDEEP
sqlselectabapdatabase-cursor
quefro
quefro

Reputation: 101

How to make massive selection SAP ABAP

I am doing a massive selection from database with the intention of saving it on application server or local directory. Since the db has loads of entries I first tried this way:

SELECT * FROM db PACKAGE SIZE iv_package
    INTO CORRESPONDING FIELDS OF TABLE rt_data
    WHERE cond  IN so_cond
    AND   cond1 IN so_cond1.

SAVE(rt_data). 
ENDSELECT.

This resulted in a dump, with the following message:

Runtime Errors: DBIF_RSQL_INVALID_CURSOR
Exeption : CX_SY_OPEN_SQL_DB

I tried doing an alternative way as well:

 OPEN CURSOR WITH HOLD s_cursor FOR
  SELECT * FROM db
    WHERE cond  IN so_cond
    AND   cond1 IN so_cond1.
  DO.
    FETCH NEXT CURSOR s_cursor INTO TABLE rt_data PACKAGE SIZE iv_package.

    SAVE(rt_data).
ENDDO.

This also resulted in a dump with the same message.

What is the best approach to this scenario?

Upvotes: 4

Views: 12016

Answers (2)

Christian
Christian

Reputation: 536

If you have a range containing only option = 'EQ' records or one of the conditions has a foreign key you can simply start looping before you do the select to reduce the size of the resulting table and move the method call out of the open cursor.

OPTION = 'EQ'

Here you just loop over the range:

LOOP AT so_cond ASSIGNING FIELD-SYMBOL(<cond>).

   SELECT * FROM db
     INTO CORRESPONDING FIELDS OF TABLE rt_data
     WHERE cond  = <cond>-low.
     AND   cond1 IN so_cond1.

   save(rt_data).

ENDLOOP.

Foreign Key

Looping over the range is not possible in this case since you cannot easily resolve the other options like CP. But you can get each value the range selects from the foreign keytab of cond. Then you loop over the resulting table and do the SELECT statement inside like above.

SELECT cond FROM cond_foreign_keytab
   WHERE cond IN @so_cond
   INTO TABLE @DATA(cond_values).

LOOP AT cond_values ASSIGNING FIELD-SYMBOL(<cond>).

   SELECT * FROM db
     INTO CORRESPONDING FIELDS OF TABLE rt_data
     WHERE cond  = <cond>.
     AND   cond1 IN so_cond1.

   save(rt_data).

ENDLOOP.

Upvotes: 0

Florian
Florian

Reputation: 5051

TYPES:
  BEGIN OF key_package_type,
    from TYPE primary_key_type,
    to   TYPE primary_key_type,
  END OF key_package_type.
TYPES key_packages_type TYPE STANDARD TABLE OF key_package WITH EMPTY KEY.
DATA key_packages TYPE key_packages_type.

* select only the primary keys, in packages
SELECT primary_key_column FROM db
    INTO TABLE @DATA(key_package) PACKAGE SIZE package_size
    WHERE cond IN @condition AND cond1 IN other_condition
    ORDER BY primary_key_column.

  INSERT VALUE #( from = key_package[ 1 ]-primary_key_column
                  to   = key_package[ lines( key_package ) ]-primary_key_column )
    INTO TABLE key_packages.

ENDSELECT.

* select the actual data by the primary key packages
LOOP AT key_packages INTO key_package.
  SELECT * FROM db INTO TABLE @DATA(result_package)
    WHERE primary_key_column >= key_package-from
      AND primary_key_column <= key_package-to.
  save_to_file( result_package ).
ENDLOOP.

If your table has a compound primary key, i.e. multiple columns such as {MANDT, GJAHR, BELNR}, simply replace the types of the from and to fields with structures and adjust the column list in the first SELECT and the WHERE condition in the second SELECT appropriately.

Upvotes: 7

Related Questions