Range Compress for dump SAPSQL_STMNT_TOO_LARGE

[oguzhans]

Introduction

When the size of the Range (as Selection Condition) is too big, system gives short-dump SAPSQL_STMNT_TOO_LARGE.

This function module will decrease the range lines without loosing data.

Details

SQL statements has a size limit for Condition Data. When ranges with line more than ~30.000 are used, system can not transfer the condition data to the Database and gives short-dump SAPSQL_STMNT_TOO_LARGE. 

For this issue "for all entries" statement is proposed but this statement causes performance problems.

The best way is to re-orginize the range data. If multiple line values are in an order, then it can be descreased to one line with operator "Between".

Range Compres function module will do this summerization automatically.

Example:

Range data Before Compress:

Sign        Option   Low        High

I              EQ          100021 

I              EQ          100024 

I              EQ          100023 

I              EQ          100022 

I              EQ          100028 

I              EQ          100034 

I              EQ          100030 

I              EQ          100031 

I              EQ          100035 

I              EQ          100026 

I              EQ          100037 

I              EQ          100036 

 Range data After Compress:

Sign        Option   Low        High

I              BT           100021  100024

I              EQ          100026 

I              EQ          100028 

I              BT           100030  100031

I              BT           100034  100037

Function Module Coding

This function module is dynamic and can be used for any type of range.

FUNCTION zpp_range_compress.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  TABLES
*"      T_ITAB
*"----------------------------------------------------------------------
  DATA : lrs_itab TYPE REF TO data, " Local Ref. Structure
         lrt_itab TYPE REF TO data, " Local Ref. Table
         lrv_low  TYPE REF TO data, " Local Ref. Variable
         lrv_high TYPE REF TO data, " Local Ref. Variable
         lrv_wa_l TYPE REF TO data, " Local Ref. Variable
         lrv_wa_h TYPE REF TO data, " Local Ref. Variable
         lrv_tmp  TYPE REF TO data. " Local Ref. Variable

  CONSTANTS : c_low(3) VALUE 'LOW'.

  FIELD-SYMBOLS : <fs_it>       TYPE STANDARD TABLE,

                  <fs>          TYPE any,
                  <fs_wa>       TYPE any,

                  <fs_sign>     TYPE any,
                  <fs_option>   TYPE any,
                  <fs_low>      TYPE any,
                  <fs_high>     TYPE any,
                  <fs_v_low>    TYPE any,
                  <fs_v_high>   TYPE any,
                  <fs_v_higher> TYPE any,
                  <fs_wa_low>   TYPE any,
                  <fs_wa_high>  TYPE any.


  CHECK t_itab[] IS NOT INITIAL.

  ASSIGN t_itab TO <fs>.
  CREATE DATA lrs_itab LIKE <fs>.
  CREATE DATA lrt_itab LIKE TABLE OF <fs>.

  ASSIGN lrs_itab->* TO <fs_wa>.
  ASSIGN lrt_itab->* TO <fs_it>.


**********************************************************************
  SORT t_itab BY (c_low).
  DELETE ADJACENT DUPLICATES FROM t_itab COMPARING (c_low).
  READ TABLE t_itab ASSIGNING <fs_wa> INDEX 1.
  ASSIGN COMPONENT 'LOW' OF STRUCTURE <fs_wa> TO <fs_low>.
**********************************************************************


**********************************************************************
  CREATE DATA lrv_low  LIKE <fs_low>.
  CREATE DATA lrv_high LIKE <fs_low>.
  CREATE DATA lrv_tmp  LIKE <fs_low>.
  CREATE DATA lrv_wa_l  LIKE <fs_low>.
  CREATE DATA lrv_wa_h  LIKE <fs_low>.

  ASSIGN lrv_low->*  TO <fs_v_low>.
  ASSIGN lrv_high->* TO <fs_v_high>.
  ASSIGN lrv_tmp->*  TO <fs_v_higher>.
  ASSIGN lrv_wa_l->* TO <fs_wa_low>.
  ASSIGN lrv_wa_h->* TO <fs_wa_high>.
**********************************************************************

  <fs_v_low> = <fs_low> .

  PERFORM increase_1 USING <fs_low> CHANGING <fs_v_higher>.

  LOOP AT t_itab ASSIGNING <fs> FROM 2.
    ASSIGN COMPONENT 'LOW' OF STRUCTURE <fs> TO <fs_low>.
    ASSIGN COMPONENT 'SIGN'   OF STRUCTURE <fs> TO <fs_sign>.
    ASSIGN COMPONENT 'OPTION' OF STRUCTURE <fs> TO <fs_option>.

    IF <fs_sign>    IS NOT ASSIGNED OR
       <fs_option>  IS NOT ASSIGNED OR
       <fs_sign>    NE 'I' OR
       <fs_option>  NE 'EQ' .
      CONTINUE.
    ENDIF.


    IF <fs_low> EQ <fs_v_higher>.
      "Aralık değeri bulundu
      <fs_v_high> = <fs_low>.

      PERFORM increase_1 USING <fs_low> CHANGING <fs_v_higher>.

    ELSE.
      IF <fs_v_high> IS ASSIGNED AND
         <fs_v_high> IS NOT INITIAL.
        <fs_wa> = 'IBT'.
      ELSE .
        <fs_wa> = 'IEQ'.
      ENDIF.

      ASSIGN COMPONENT 'LOW'  OF STRUCTURE <fs_wa> TO <fs_wa_low>.
      ASSIGN COMPONENT 'HIGH' OF STRUCTURE <fs_wa> TO <fs_wa_high>.

      <fs_wa_low> = <fs_v_low>.
      <fs_wa_high> = <fs_v_high>.

      APPEND <fs_wa> TO <fs_it>.

      CLEAR: <fs_v_high>, <fs_wa>.

      <fs_v_low> = <fs_low>.
      PERFORM increase_1 USING <fs_low> CHANGING <fs_v_higher>.

    ENDIF.
  ENDLOOP.

  IF <fs_v_high> IS ASSIGNED AND
     <fs_v_high> IS NOT INITIAL.
    <fs_wa> = 'IBT'.
  ELSE .
    <fs_wa> = 'IEQ'.
  ENDIF.

  ASSIGN COMPONENT 'LOW'  OF STRUCTURE <fs_wa> TO <fs_wa_low>.
  ASSIGN COMPONENT 'HIGH' OF STRUCTURE <fs_wa> TO <fs_wa_high>.

  <fs_wa_low> = <fs_v_low>.
  <fs_wa_high> = <fs_v_high>.

  APPEND <fs_wa> TO <fs_it>.

  t_itab[] = <fs_it>.

ENDFUNCTION.


FORM increase_1 USING p_value CHANGING p_value_n.
  DATA: v_hx(1) TYPE x,
        v_len   TYPE i,
        v_off   TYPE i.

  p_value_n = p_value.

  FIELD-SYMBOLS <fs>.

*write:/ 'Initial:', p_value_n.
  v_len = strlen( p_value_n ).

  v_off = v_len - 1.

  DO v_len TIMES.
    IF p_value_n+v_off(1) BETWEEN 'A' AND 'Z'.
      IF p_value_n+v_off(1) LT 'Z'.
        SEARCH sy-abcde FOR p_value_n+v_off(1).
        DATA(lv_fdpos_next) = sy-fdpos + 1.
        p_value_n+v_off(1) = sy-abcde+lv_fdpos_next(1).
        EXIT.
      ELSE.
        p_value_n+v_off(1) = 'A'.
      ENDIF.
    ELSEIF p_value_n+v_off(1) BETWEEN '0' AND '9'.
      IF p_value_n+v_off(1) LT '9'.
        p_value_n+v_off(1) = p_value_n+v_off(1) + 1.
        EXIT.
      ELSE.
        p_value_n+v_off(1) = '0'.
      ENDIF.

    ELSE. "Will not work for special characters
      EXIT.
    ENDIF.
    v_off = v_off - 1.
  ENDDO.

*write:/ 'Final:', p_value_n.

ENDFORM.

Notes

The FM usage is pretty easy. Just call the FM in your development with the range table.

For standart programs, it has to be implemented with an enhancement or exit (if possible).

FM will run only for lines that has "I" as SIGN and "EQ" as OPTION.

Conclusion

This FM is a plug and play design. But If you have any question, please ask. I will be checking the blog for Q&A. I am looking forward to share and learn.