How to Simulate a Where Clause in SQ00 without ABAP

Putting the veneer of ABAP on something that is not ABAP is never quite as good as the real thing, but in a pinch it sometimes has to suffice. Reporting with the ability to recurse queries, call functions and perform complex string functions is always more desirable than calling a simple query.

Simple reporting backed by ABAP in the form of a report is a great solution for many organizations, but it is a costly endeavor for large bureaucratic organizations that have outsourced the management of their SAP implementation to larger, more bureaucratic organizations. Empowering clients to produce their own reports by embedding simple ABAP in SQ02 Infosets eliminates the need for costly external development which leads to arbitrary prohibitions against complex queries "because performance issues (sic)".

Using LSMW for the purpose of reporting is an easy way to quickly develop complex reports, but clients are unable to utilize the reports written in LSMW without permissions in LSMW. Where SQ00 is available, simple and slightly complex queries can still be used to produce reports.

Examples from real life

This example query was written to create a list of customers that have been blocked Centrally or at the Company Code level or at the Sales Organization level. Because there is no way on an SQ00 selection screen to request results where one field is not blank or another field is not blank, the only option with the simplest query is to check each field individually.

There are 16 fields that can cause a customer to be blocked in ECC and it is unreasonable to require a user to run the same query 16 times to determine which customers are somehow blocked in any way at any level. With ABAP, the simplest solution would be to concatenate the 16 fields and report all non-blank results, but the door to ABAP is closed. However, there is still an open window . . .

This example starts in SQ02 as a simple join between KNA1, KNB1 and KNVV. Only the fields desired for the query are populated in the Field Groups.

Create an Infoset Query in SQ01 using the newly created Infoset. Navigate using Goto→Field selection→Field selection to get to Field Selection.

Switch on Short names and then provide short names to the table fields that will be used to formulate the where clause. In this example, only one letter was used for each field in order to maximize the amount of comparisons that can be included on each line--this will make more sense later.

Then navigate to Edit→Local→Create in order to create a Local Field. This next step will go very smoothly if all the fields that will be used have been assigned a short name.

Now create the new local field to hold a value whenever any of the fields that indicate a customer is blocked or flagged for deletion is populated. The name is important as the client will see it on the selection screen.

Simplicity lost or K.I.C.S

Now click the "Complex Calculation" button. The button is aptly named and allows the programmer to perform some complex work.

There are three calculations that populate this field with the name of the table from which the blocked or deletion indicator came. Here I = 'X' is used for SPERR, but I <> '' would have worked just as well.

One can see here the pragmatism of using one character as a short name; it is possible to put as many as 5 comparisons on one line. If more lines are required then clicking the Insert button while the cursor is on a line of code will add an additional blank line right under it.

Save this complex calculation and then the Local Field and then the query. Now any time that any of the fields is populated, this field will be too.

All that is left now is to create a variant that defaults the query to list only records where the field ISBLOCKED is a non-blank. This will save the client the trouble of having to click F2 each time the query is run and manually specify that only non-blank values will be displayed.

This code is absolutely not ABAP and is much closer to the old Basic language embedded in TRS-80's. Proper math symbols like =, <, >, <>, /, +, -, and, or, (, ) can be used, but functions and strings appear to have been left out so the most complex where clauses are still reserved for ABAP.