*                _   _                               _       _           _
*               /_\ | |__   __ _ _ __   ___ __ _  __| | __ _| |__  _ __ /_\
*              //_\\| '_ \ / _` | '_ \ / __/ _` |/ _` |/ _` | '_ \| '__//_\\
*             /  _  \ |_) | (_| | |_) | (_| (_| | (_| | (_| | |_) | | /  _  \
*             \_/ \_/_.__/ \__,_| .__/ \___\__,_|\__,_|\__,_|_.__/|_| \_/ \_/
*                               |_|                      www.abapcadabra.com
*----------------------------------------------------------------------------
* program          : ZABAPCADABRA_LOADBALANCE_TABLE
* title            : Loadbalance a table: list key values
* functional area  : Cross modules
* environment      : 4.7
* program Function : This report can be used to determine which key values
*                    should be used to select a given blocksize of entries.
*                    So instead of "select 1000 lines" you can determine which
*                    key field is the 1000th in the table.
* Documentation    : Search for "Loadbalance table content" on AbapcadabrA.com
* Previous version : This is the initial version
* Developer name   : Wim Maasdam
* Development date : 03/10/2017
* Version          : 0.1
*---------------------------------------------------------------------
* Change list:
*   Date       Description
*   03/10/2017 Initial release
*   29/11/2017 Output as from/to value sets, input-ready fields for
*              eay copying. Input fields - text support
*   06/12/2017 Added SQL addition - to cater for the RBKP table.
*---------------------------------------------------------------------
REPORT ZABAPCADABRA_LOADBALANCE_TABLE.

*  __      _           _   _
* / _\ ___| | ___  ___| |_(_) ___  _ __    ___  ___ _ __ ___  ___ _ __
* \ \ / _ \ |/ _ \/ __| __| |/ _ \| '_ \  / __|/ __| '__/ _ \/ _ \ '_ \
* _\ \  __/ |  __/ (__| |_| | (_) | | | | \__ \ (__| | |  __/  __/ | | |
* \__/\___|_|\___|\___|\__|_|\___/|_| |_| |___/\___|_|  \___|\___|_| |_|
selection-SCREEN BEGIN OF LINE.
selection-SCREEN COMMENT 1(13) lbl_01 FOR FIELD pa_table.
parameter: pa_table type dd02l-tabname default 'MARA'.
selection-SCREEN END OF LINE.
selection-SCREEN BEGIN OF LINE.
selection-SCREEN COMMENT 1(13) lbl_02 FOR FIELD pa_bsize.
parameter: pa_bsize type n length 10 default 100000.
selection-SCREEN END OF LINE.
selection-screen skip.
selection-SCREEN BEGIN OF LINE.
selection-SCREEN COMMENT 1(13) lbl_03 FOR FIELD pa_sql.
parameter: pa_sql type c length 140 visible length 60 lower case.
selection-SCREEN END OF LINE.
selection-SCREEN BEGIN OF LINE.
selection-SCREEN COMMENT 15(70) cmt_03 modif id blu.
selection-SCREEN END OF LINE.
selection-SCREEN BEGIN OF LINE.
selection-SCREEN COMMENT 15(70) cmt_04 modif id blu.
selection-SCREEN END OF LINE.

AT SELECTION-SCREEN OUTPUT.
  LOOP AT SCREEN.
    if screen-group1 = 'BLU'.
      screen-INTENSIFIED = '1'.
      modify screen.
    endif.
  ENDLOOP.

INITIALIZATION.
* All texts for this report have been set up as hard-coded texts
  lbl_01 = 'Tablename'.
  lbl_02 = 'Block size'.
  lbl_03 = 'SQL addition'.
  cmt_03 = 'MTART = ''HAWA'' AND LVORM = SPACE'.
  cmt_04 = 'GJAHR BETWEEN ''2015'' AND ''2017'''.

*  __ _             _             __     __      _           _   _
* / _\ |_ __ _ _ __| |_     ___  / _|   / _\ ___| | ___  ___| |_(_) ___  _ __
* \ \| __/ _` | '__| __|__ / _ \| |_ ___\ \ / _ \ |/ _ \/ __| __| |/ _ \| '_ \
* _\ \ || (_| | |  | ||___| (_) |  _|___|\ \  __/ |  __/ (__| |_| | (_) | | | |
* \__/\__\__,_|_|   \__|   \___/|_|     \__/\___|_|\___|\___|\__|_|\___/|_| |_|
start-of-selection.

  data: lt_dd03l_fieldname type standard table of dd03l-fieldname,
        lv_fieldname type dd03l-fieldname,
        lv_table_size type sy-dbcnt,
        lt_tablekeys type standard table of char20,
        lv_tablekey_prev type char20,
        lv_tablekey type char20,
        lv_slices type n length 3,
        lv_position type sy-dbcnt.

* Find the key fields of this table, should only be 1 field (ignoring client)
  select fieldname from dd03l into table lt_dd03l_fieldname
    where tabname = pa_table and
          fieldname <> 'MANDT' and
          as4local = 'A' and
          as4vers = 0000 and
          keyflag = abap_true.
  if sy-subrc <> 0.
    message 'Invalid table' type 'E'.
  endif.
  if lines( lt_dd03l_fieldname ) <> 1.
    if pa_sql is initial.
      message 'Multiple key fields (supply additional SQL statement)' type 'E'.
    else.
      message 'Multiple key fields' type 'S'.
    endif.
  endif.

  if pa_sql is initial.
    select count( * ) from (pa_table) into lv_table_size.
  else.
    TRY.
        select count( * ) from (pa_table) into lv_table_size
          where (pa_sql).
      CATCH cx_sy_dynamic_osql_error cx_sy_open_sql_db.
        message 'SQL additional: selection error' type 'E'.
    ENDTRY.
  endif.

  write: /(12) 'Table :' right-justified, pa_table,
         /(12) 'Entries :' right-justified, lv_table_size left-justified,
         /(13) '-:-' right-justified.
  lv_slices = lv_table_size div pa_bsize.

  read table lt_dd03l_fieldname into lv_fieldname index 1.
  TRY.
      select (lv_fieldname) from (pa_table) into table lt_tablekeys
        where (pa_sql).
    CATCH cx_sy_dynamic_osql_error cx_sy_open_sql_db.
      message 'SQL additional: selection error (2)' type 'E'.
  ENDTRY.

  sort lt_tablekeys.
  do lv_slices times.
    lv_position = ( (  sy-index - 1 ) * pa_bsize ) + 1.
    read table lt_tablekeys index lv_position into lv_tablekey_prev.
    lv_position = sy-index * pa_bsize.
    read table lt_tablekeys index lv_position into lv_tablekey.
    if sy-subrc = 0.
      write: / lv_position no-gap, ':', lv_tablekey_prev INPUT, 'to', lv_tablekey INPUT.
    endif.
  enddo.

  add 1 to lv_position.
  read table lt_tablekeys index lv_position into lv_tablekey_prev.
  if sy-subrc = 0.
    read table lt_tablekeys index lv_table_size into lv_tablekey.
    if sy-subrc = 0.
      write: / lv_table_size no-gap, ':', lv_tablekey_prev INPUT, 'to', lv_tablekey INPUT.
    endif.
  endif.