Simulate SQ00 in LSMW Using Dynamic Selection and Dynamic Internal Table Creation

Anybody who has been working with SAP long enough has run into a BASIS team that has banned the use of SQ00 "because performance". Despite the unbelievable usefulness of the query tool, the BASIS team won't permit users to access SQ00 in production, even when the query is optimized with ABAP.

There is plenty of source code shared around the 'net that explains how to dynamically read a single transparent table dynamically into an internal table created dynamically, but until now, there was no code that created an internal table dynamically based on a dynamic select statement. This code makes it easy to extract joined tables with max(), min() or count() into a dynamic internal table than can then be used to populate an Excel spreadsheet, to fill a text file or to print to the screen.

This LSMW object makes custom reporting easy for those who understand OpenSQL. It starts in GLOBAL_DATA by collecting values for what fields will be selected, from where the fields will be selected, the "where" filter that limits the number of records and the group by that enables additional functions like Max(), Min() and Count().

The parameters are enriched with descriptions to make using the tool easy. There is no need to include reserved words like select, from or where; any of those will cause the query to fail and throw and error.

TYPE-POOLS : abap.
FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,
             <dyn_wa>,
             <dyn_field>.
DATA: dy_table TYPE REF TO data,
    dy_line  TYPE REF TO data,
    xfc TYPE lvc_s_fcat,
    ifc TYPE lvc_t_fcat.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
  selection-screen begin of line.
    selection-screen comment 1(12) cofields.
    PARAMETERS: p_fields type string obligatory.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(12) cofrom.
    PARAMETERS: p_from type string obligatory.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(12) cowhere.
    PARAMETERS: p_where type string.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(12) cogroup.
    PARAMETERS: p_group type string.
  selection-screen end of line.
  selection-screen begin of line.
    selection-screen comment 1(12) coorder.
    PARAMETERS: p_order type string.
  selection-screen end of line.
SELECTION-SCREEN END OF BLOCK b1.
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME.
  selection-screen begin of line.
    PARAMETERS : P_RAD1 RADIOBUTTON GROUP RB1 default 'X'.
    selection-screen comment 3(20) coscreen.
  selection-screen end of line.
  selection-screen begin of line.
    PARAMETERS : P_RAD2 RADIOBUTTON GROUP RB1.
    selection-screen comment 3(14) cofile.
    parameters: p_file type string.
  selection-screen end of line.
  selection-screen begin of line.
    PARAMETERS : P_RAD3 RADIOBUTTON GROUP RB1.
    selection-screen comment 3(16) coexcel.
    parameters: p_friend as checkbox default 'X'.
    selection-screen comment 23(30) cofriend.
  selection-screen end of line.
SELECTION-SCREEN END OF BLOCK b2.
parameters: p_break as checkbox.
initialization.
cofields = 'Select:'.
cofrom = 'From:'.
cowhere = 'Where:'.
cogroup = 'Group by:'.
coorder = 'Order by:'.
coscreen = 'Write to screen.'.
cofile = 'Write to file:'.
coexcel = 'Present in Excel.'.
cofriend = 'Friendly field names.'.

data: cField(40) type c, nFields type i, cTable(20) type c,
      cFieldsList(300) type c, cFields(300) type c.
data: idetails TYPE abap_compdescr_tab,
      xdetails TYPE abap_compdescr,
      ref_table_des TYPE REF TO cl_abap_structdescr,
      rowStructDescr TYPE REF TO cl_abap_structdescr,
      rowReference TYPE REF TO data,
      it_Fields type table of char40.

types: begin of t_FieldHeader,
        header(3000) type c,
      end of t_FieldHeader.
data: it_FieldHeader type standard table of t_FieldHeader
      initial size 0,
      wa_FieldHeader(3000) type c.

Next, the system interprets the fields and knits together a internal table based on the field types coming from the source tables. It recycles the name of the field from the source table as the name of the field in the dynamically-created internal table.

if p_break eq 'X'. break-point. endif.
if p_Fields eq '*'. "Assumes the FROM is just a table name.
  ref_table_des ?= cl_abap_typedescr=>describe_by_name( p_From ).
  idetails[] = ref_table_des->components[].
  LOOP AT idetails INTO xdetails.
    append xDetails-NAME to it_Fields.
  endloop.
else.
  cFields = p_Fields.
  translate cFields to UPPER CASE.
  " Now, handle the Max, Min and Count fields as simply as possible.
  do 50 times. "Catch at least 50 of these . . .
    replace 'AVG( ' with '' into cFields.
    replace 'MAX( ' with '' into cFields.
    replace 'MIN( ' with '' into cFields.
    replace 'SUM( ' with '' into cFields.
    replace 'COUNT(*)' with 'COUNT' into cFields. "Handled later.
    replace 'COUNT( ' with '' into cFields.
    replace 'DISTINCT' with '' into cFields.
    replace ' )' with '' into cFields.
    replace '  ' with ' ' into cFields. "Catch double spaces...
  enddo.
  split cFields at space into table it_Fields.
endif.
loop at it_Fields into cField.
  "Check for empty fields caused by double spaces...
  add 1 to nFields.

  translate cField to UPPER CASE.
  if cField cs '~'.
    split cField at '~' into cTable cField.
  else.
    cTable = p_From.
  endif.
  "FieldHeader used later if exporting to text file . . .
  if wa_FieldHeader eq ''.
    wa_FieldHeader = cField.
  else.
    concatenate wa_FieldHeader cField into wa_FieldHeader
     separated by cl_abap_char_utilities=>horizontal_tab.
  endif.
  if cField eq 'COUNT'.  "Make this a floating-point integer.
    xfc-fieldname = 'COUNT'.
    xfc-datatype = 'DEC'.
    xfc-inttype = 'P'.
    xfc-intlen = 8.
    xfc-decimals = 0.
    APPEND xfc TO ifc.
  else.
    ref_table_des ?= cl_abap_typedescr=>describe_by_name( cTable ).
    idetails[] = ref_table_des->components[].
    LOOP AT idetails INTO xdetails where NAME eq cField.
      CLEAR xfc.
      xfc-fieldname = xdetails-name.
      if cFieldsList cs xfc-fieldname.
        " If the table already has this fieldname then the second
        " occurance will have a zero added to it.
        concatenate xfc-fieldname '0' into xfc-fieldname.
      endif.
      concatenate cFieldsList ',' xfc-fieldname into cFieldsList.
*   Correction by Paul Robert Oct 28, 2009 17:04 *** Thanks! -Jimbo
*      xfc-datatype = xdetails-type_kind.
      CASE xdetails-type_kind.
        WHEN 'C'.
          xfc-datatype = 'CHAR'.
        WHEN 'N'.
          xfc-datatype = 'NUMC'.
        WHEN 'D'.
          xfc-datatype = 'DATE'.
        WHEN 'P'.
          xfc-datatype = 'PACK'.
        WHEN OTHERS.
          xfc-datatype = xdetails-type_kind.
      ENDCASE.
      xfc-inttype = xdetails-type_kind.
      xfc-intlen = xdetails-length.
      xfc-decimals = xdetails-decimals.
      APPEND xfc TO ifc.
    ENDLOOP.
  endif.
endloop.

  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog  = ifc
      i_length_in_byte = 'X' "added by Paul Robert Oct 28, 2009 17:04
    IMPORTING
      ep_table         = dy_table.
  ASSIGN dy_table->* TO <dyn_table>.
* Create dynamic work area and assign to FS
  CREATE DATA dy_line LIKE LINE OF <dyn_table>.
  ASSIGN dy_line->* TO <dyn_wa>.

Next, the system calls the dynamic SQL Query and uses the results to populate the dynamically-created internal table. The structure of the internal table matches perfectly the output of the query because the field types come from the tables in the query.

SELECT (p_Fields)
 into table <dyn_table>
 from (p_From)
 where (p_Where)
 group by (p_Group)
 order by (p_Order).

Finally, based on the radio button selected on the first screen, another snippet of code is launched to handle the data retrieved. The Excel spreadsheet option requires this snippet of code and the option to export to text requires this snippet of code and both are include in Jimbo's LSMW Toolbox.

if p_Rad1 eq 'X'.  "Write to screen . . .
  loop at <dyn_table> into <dyn_wa>.
    do nFields times.
      assign component sy-Index of structure <dyn_wa> to <dyn_field>.
      if sy-index eq 1.
        write: / <dyn_field>.
      else.
        write: <dyn_field>.
      endif.
    enddo.
  endloop.
endif.
if p_Rad2 eq 'X'.  "Write to text file . . .
  if p_File eq ''.
    write: / 'No file name specified.'.
  else.
    append wa_FieldHeader to it_FieldHeader.
    perform ExportTableWrite using it_FieldHeader p_File ''.
    perform ExportTableWrite using <dyn_table> p_File 'X'.
  endif.
endif.
if p_Rad3 eq 'X'.  "Launch Excel and show there . . .
  if p_friend eq 'X'.
    perform InternalTableToExcel using <dyn_table> 1 sy-langu.
  else.
    perform InternalTableToExcel using <dyn_table> 1 ''.
  endif.
endif.

Download:
Download this tool along with the rest of Jimbo's LMSW Toolbox from this link. The downloaded version includes more features and some error handling code.

Credit:
Special thanks to Sandra Rossi and Paul Roberts for this great work explaining how to dynamically build a table and then a structure from it.