How to Embed ABAP Code in a SQ00 Query

Coaxing SAP into presenting useful information using SQ00 can be a daunting task if the complexity of the data is spread over more than two tables. It is not uncommon for clients to request disparate data sources be knit together in order to make sense of it for a particular scenario.

Writing a report for the same requested information can be a snap because of the way that ABAP allows one to manipulate tables, call functions and filter data based on complex requirements. Basic SQ00 queries don't offer such enhanced manipulation capabilities.

Naturally, SAP generates complex ABAP code any time a query is created despite looking incredibly serine and placid. In the case of an SQ02 Infoset, an internal table called %dtab is populated and then used as the container for the query which then filters the results, orders the columns, populates the table and presents the results.

Escaping SQ00 Limitations

The secret to using SQ01 to generate these meaningful reports for use by everyday clients in SQ00 is hijacking that internal table and populating it with data using complex ABAP algorithms. That's where the sample report for this article began . . .

The client wanted a report that knit together information from the material classification, BOM and units of measure tables. Rendering a report with just the classifications listed on a single line is made impossible because of the way materials are linked to classifications and classifications are linked to characteristics and characteristics are linked to values, but the client wanted those values on each line next to a single BOM item and the EAN (barcode) from the Units of Measure table next to related BOM components based on the material description of the BOM component and the Unit of Measure associated with the EAN.

Writing a report or teasing the data out of SAP using LSMW is an hour-long project, but joining MAST with STPO, MARM and multiple copies of MAKT, CABN, AUSP, CAWN and CAWNT in SQ00 to produce the same report is impossible--especially when SAP refuses to join a MATNR field to an OBJEK field. The BOM alone presents the first impasse as it cannot be rendered in a meaningful fashion if any change numbers have been applied to it; SAP does not include the valid-to date (DATUV) in the component table.

Getting Started

Create an InfoSet with enough fields to hold all of the data from the generated report. The field type matters as SAP will slip in some under-the-hood manipulation to some fields and the fields must be long enough to hold the proper data.

MAKT is a great example of a table with long, easy-to-fill fields. It has a nearly-free-text MATNR component that is 18 characters and two 40-character free-text fields that can be populated with any character data.

MAKT is also convenient as it will always produce results when joined to MARA, MARC or MAST. The SQ00 transaction verifies that the join has returned some records to process before trying to process them; if the dummy tables are arranged in a join that returns no records then it will fail before the internal table can be hijacked. It also checks after the table has been filled to ensure that there are some records, but that will be covered a little later at the last line of the sample code.

In SQ02, go to the desired query area by clicking Environment → Query areas. Most shops avoid cross-client work as it entails a lot of work and the Client-specific work area is fine for systems with just one client.

Next, create an InfoSet with a handful of tables starting with the main table. In the case of this example, the main table was MAST as each record in the query would be associated with a BOM component. It's possible to include multiple copies of a single table by creating aliases for them. Just click the Alias button (seen with three yellow squares in the image below) and create as many as are needed.

Next, put the fields in a Field Group. This can be automated, but putting them in a single group manually is much easier to maintain later.

Change the headers on the fields to reflect the desired field contents. Instead of "Material Description", the header can be something like, "Pounds" for a unit of measure or "Wheel size" for a characteristic value.

Before saving the InfoSet, add a break-point to the ABAP code under Record Processing. This will be used later to get the structure of %dtab so that it can be mirrored manually.

data: isDone(1) type c.
if isDone ne 'X'.
  isDone = 'X'.
  break-point.
endif.

Save the InfoSet, and generate the infoset. Then launch SQ01 to make a query that uses this InfoSet by clicking the InfoSet Query button and then selecting the newly created InfoSet.

Now select the fields that will be in the report in the order that they will be in the report. This can be adjusted later, but it's very cumbersome because of the way SAP rewrites the ABAP code under the query and adjusts the structure to fit the query results.

Save the query and then run it. The debugger will launch and the structure of %dtab will be visible. At this point the programming can begin.

Based on the structure above, the code to populate this table should look like this. This goes in the InfoSet in the same place as the break-point. Notice that the query increments the %l_hits_cnt variable ever time a record is appended to the internal table--that has to be addressed, too.

  if isDone ne 'X'.
    data: lMARA like MARA, lMAST like MAST, lSTKO like STKO,
*        lSTPO like STPO,
          lMARM like MARM, lKSSK like KSSK,
          lKLAH like KLAH, lKSML like KSML, lCABN like CABN,
          lAUSP like AUSP, lMAKT like MAKT, lCAWN like CAWN,
          lBISMT like MARA-BISMT,
          fFloat type P decimals 2.
    FIELD-SYMBOLS <dtab> type STANDARD TABLE.
    ASSIGN ('%dtab[]') TO <dtab>.
    FIELD-SYMBOLS <hits> type i.
    assign ('%l_hits_cnt') TO <hits>.
    types: begin of sdtab,
        MATNR like MAST-MATNR,
        MAKTX like MAKT-MAKTX,
        MATNR001 like MAKT-MATNR,
        MAKTX001 like MAKT-MAKTX,
        MATNR002 like MAKT-MATNR,
        WERKS like MAST-WERKS,
        MATNR003 like MAKT-MATNR,
        ANNAM like MAST-ANNAM,
        MAKTX002 like MAKT-MAKTX,
        AENAM like MAST-AENAM,
        MAKTG like MAKT-MAKTG,
        MAKTG001 like MAKT-MAKTG,
        MAKTG002 like MAKT-MAKTG,
           end of sdtab.
    data: wa_dtab type sdtab.
    DATA: lSTPOB LIKE STPOB OCCURS 1 WITH HEADER LINE.
    DATA: BEGIN OF LOC_CSZALT_TAB OCCURS 1.
            INCLUDE STRUCTURE CSZALT.
    DATA: END   OF LOC_CSZALT_TAB.
    isDone = 'X'. break jkaufmann. "Client doesn't see debugger
    "Start by pulling up the BOM for the Reference material.

The wa_dtab structure must be created manually to mirror the internal table %dtab because it doesn't exist in the source code of the InfoSet--only the query. When the structure is populated, it is appended to the internal table %dtab using a field-symbol that points to the internal table %dtab.

In this example, the BOM components are pulled into an internal table using the GET_STPO function. This gets only the current BOM components--not all of the records in the STPO table.

    select * from MAST into lMAST
      where MATNR in sMATNR
      and WERKS in sWERKS
      and STLAN eq '1'
      and STLAL eq '01'.
      clear lSTPOB.
      refresh lSTPOB.
      lSTPOB-STLTY = 'M'.  "Material
      lSTPOB-STLNR = lMAST-STLNR.
      call function 'GET_STPO'
         exporting
             ALL = 'X'
             ALTER = '01'
             DATUB = SY-DATUM
             DATUV = SY-DATUM
             NO_BUFFER = 'X'
             SET = 'X'
             VALID = 'X'
         TABLES
             ADD_WA = LOC_CSZALT_TAB
             WA = lSTPOB
         EXCEPTIONS
              CALL_INVALID     = 1
              END_OF_TABLE     = 2
              GET_WITHOUT_SET  = 3
              KEY_INCOMPLETE   = 4
              KEY_INVALID      = 5
              NO_RECORD_FOUND  = 6
              VIEWNAME_INVALID = 7
              OTHERS           = 8.
      loop at lSTPOB.
        clear wa_dtab.
        wa_dtab-MATNR = lMAST-MATNR.
        wa_dtab-WERKS = lMAST-WERKS.
        select single MAKTX from MAKT into wa_dtab-MAKTX
         where MATNR eq lMAST-MATNR.
        wa_dtab-MAKTG = lSTPOB-IDNRK.
 
[ code skipped for brevity ]

          select * from CABN into lCABN
           where ATNAM eq 'WHEEL_SIZE'.
            select * from AUSP into lAUSP
             where OBJEK eq lMAST-MATNR
               and ATINN eq lCABN-ATINN.
              wa_dtab-AENAM = lAUSP-ATWRT.
            endselect.
          endselect.
        endselect.
        append wa_dtab to <dtab>.
        add 1 to <hits>.
      endloop.  "at lSTPOB
    endselect.
  endif.
  "Now stop the query from adding any new records.
  check 0 eq 1.

The check 0 eq 1. line at the end ensures that the query does not add any additional records to the results and also prevents it from incrementing the %l_hits_cnt variable. If the %l_hits_cnt is not incremented by the custom code then it would be zero at the end and SQ00 will throw a "No records selected" error. That is why it is incremented in the custom ABAP code using the <hits> field-symbol.

Warning: This will most likely need to be created in a development system where you have developer privileges and not in a production system where your users need it. Moving it will require a transport request if you do not have development privileges there, but if you do then you can transport the queries yourself.

Credit:
It is important for credit to be given where it is due. Thank you to feimster at SAP Tribal Knowledge for this great article explaining the hidden workings of SQ00.