How to add new codes into SAP Query/Infoset

[Former Member]

Hi Experts,

First time working with SAP Query.

I need to enhance an existing Query to add a new field into a report. I have added new tables into this Infoset and joined my new tables with existing tables and able to extract the new fields out on the report. But here are my problems:

Example of original output:

X88888 500000

X99999 400000

Example of new output after adding my 2 new tables to get 1 new field to output:

X88888 50000 01/08/2009

X88888 50000 01/25/2009 <--- for X88888 customer, I want only this record w/ latest date to be output

X88888 50000 01/22/2009

X99999 40000 03/09/2009

X99999 40000 04/18/2009

X99999 40000 04/19/2009 < -- for X99999, I want only this record w/ latest date to be output

Where/How i can add the code to control this logic so that only 1 record per customer with the highest date can be output. I looked at the query program and it looks very confusing and hard to understand.

Please advise,

Thanks,

Sam

[Sandra_Rossi]

the problem here is that you need to wait that all records are read, to be able to sort and delete invalid records. In SAP Query, you may put code only during the extraction of records (records are not sorted at that time).

So, the best solution is to remove your tables from the join (at least the table which multiply the records), and read them using an abap select inside the code of the additional field, where you take into account only the more recent date.

[Former Member]

Hi Sandra - i am so sorry for replying this so late.. I really appreciate your suggestions. I found another existing table that gives me the unique date that i need...

Again, thank you..

[Former Member]

Hi Sam,

Instead on adding new table/tables , better way to use Direct Read from your single table.

Please read the below steps to suffice your requirement.

Assumption: Let your original table be A and second table is B.

Tables A and B are having field X1 and X respectively, using these fields, they can be joined.

You want field date1 of table B.

Infoset Changes:

1. Go to SQ02 (Enter Infoset name, click on Change button)

2. Create an extra field E_date of type date (Sy-datum), by clicking on 'Extras' (F5) button.

3. Select that field in one of the field group of your Infoset.

4. Click on Code Icon (Shift+F8) displayed on application bar, you will be getting lists events in 'Code section'.

5. In Data Section , declare an internal table like below code (here pseudo code)

data: begin of itab_date occurs 0, 
                   date1  like B-date1,
                   end of itab_date.

6. In Recod processing Event write the following code.

CLEAR itab_date.
CLEAR E_date.

SELECT date1 FROM B into table itab_date                           " Retrieving date field from table B
WHERE X = A-X1.

Sort itab_date by itab_date-date1  DESCENDING.                 "Sort internal table B in decendind order on date1

READ TABLE itab_date index 1.                                            "Read first record of interanal table
E_date  = itab_date-date1                                                      "Assigning top most date of internal table into field E_date.

7. Generate the Infoset

Query changes

1.Go To SQ01 (in a new session),

2.Give your Query name, click on change button, and select that field group in which you have added extra field.

3.Select extra field.

4.Go to basic list to select that field in your O/p list.

5.Click on Test button, and see if you are getting desired result.

Please let me know if you need further details.

Regards,

Dinesh

[Former Member]

Hi Sam,

Did you try to implement the given logic in your Infoset.

Regards,

Dinesh

[Former Member]

Dinesh - thank you very much for your help.. I found another table that has the unique dates that solved my problems..

After looking into your suggestions, I think what you have suggested for me to do would work. I will definitely employ your suggestions in my next SAP Query PR's.

Thank you so much for your helpful suggestions..

Regards,

Sam