How do I do a SELECT DISTINCT using a SAP Infoset Query?

Question

For Example: Using table ITOB; if I select EQUNR and ANLNR, I get multiple entries (mostly due to non-unique ILOAN, TPLNR and DATBI records). How can I do the equivalent of:

select distinct equnr anlnr from itob into (itob-equnr, itob-anlnr)
  where (some_wherclause).

Is there a checkbox or radiobutton somewhere that I'm missing that will toggle distinct entries only?

OR

In the CODE areas unter extras what variables do I have available to me? If I could access the result set, I could delete the duplicates there. I have tried accessing %dtab that is used in the generated program, but no luck.

Answer 1

In the CODE areas unter extras what variables do I have available to me?

From what I can see only the header lines of the selection tables that you define in the join condition is available in the coding blocks. I.E. if you are selecting from ITOB join ANLA, both header lines ITOB and ANLA are available. (This is only relevant in the RECORD PROCESSING block). However, you can define your own variables in the DATA block.

How to access the data set & limit the records after the selection:

Disclaimer: This feels very much like a hack, it does not work for all the output types, but it does show how you could access the data set.

In code block INITIALIZATION add the following statement:

* Dummy comment to force generation 
* of END-OF-SELECTION Block

Any comment will do - all this does is force the program generator to generate the END-OF-SELECTION block which we need to access the data set.

In code block END-OF-SELECTION add the following:

data: lv_table type char100 value '%G00[]'.
field-symbols: <table> type any table.
assign (lv_table) to <table>.
if <table> is assigned.
  sort <table>.
  delete adjacent duplicates from <table>.
endif.

%G00 is the variable name of the resultset in the generated program (use [] to access the table, not just the header line). As the generated program variables are not available in the Infoset, you have to use field-symbols to access any variables. If the variable does not exist, the check for assignment will avoid any run-time errors.

Limitation: When executing the query in "ABAP List mode" this code has no effect, this is because in this case the list is written as the data is being selected, therefore any manipulation after the fact is too late.

What I ended up doing:

I did a manual join of the relevant individual tables in ITOB (EQUI->EQUZ->ILOA) and left IFLOT and IFLOTX out of the mix, this only required me to put an additional restriction on end date. I guess in most cases it is possible to deconstruct the data to a level where you can avoid duplicates by using the correct WHERE clause. The question of how to do a SELECT DISTINCT when you truly need to remains an open one...