Search single row on SQ01 report

[sapmgon]

Greetings,

I'm currently working on a Query through SQ02 to obtain the data from an equipment (similar to what IQ03 brings but through a report), right now this is an example of what i'm finding:

The requirement that I'm having trouble with comes at the join from the Equipment (EQUNR), the Object List Number (OBJK-OBKNR) and the Structure element (VIAUFKST-PROID):

The idea would be that, for any EQUNR (non key) given to the OBJK table, a single line with the max date (OBJK-DATUM) would bring me the required Object list number (OBJK-OBKNR), to then use on the VIAUFKST table and show the correct PROID on the report.

As an example, I have the next OBJK data:

It should only bring the 2nd and 3rd row, because they're a different Equipment and having the max data available for each one of them, giving me the Object List Numbers "1.204" and "1.205" to then search through the VIAUFKST table.

I tried coding through SQ02, but it only fetches a single line (in this case the 2nd one for being the max date), giving me a hard time to differentiate from equipment and testing how to give this info to the report through "Record Processing"

Right now what i'm working with is:

DATA

DATA: BEGIN OF it_objk OCCURS 0,
  obknr LIKE objk-obknr,
  equnr LIKE objk-equnr,
  objvw LIKE objk-objvw,
  datum LIKE objk-datum,
END OF it_objk.

START-OF-SELECTION

SELECT obknr equnr objvw datum
  FROM objk INTO TABLE it_objk
  up to 1 rows
  WHERE objvw = 'A'
  GROUP BY obknr equnr objvw datum
  ORDER BY datum DESCENDING.
LOOP AT it_objk.
  WRITE: / it_objk-obknr,
  it_objk-equnr,
  it_objk-objvw,
  it_objk-datum.
ENDLOOP.

Another example of what we want to reach would be like this (considering that the actual data has from 2 to 12 different OBKNR fields for every EQUNR):

Thanks in advance.

[AnnePetteroe]

Hello Mathias,

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers, as it provides tips for preparing questions that draw responses from our members.

Feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will also help you when preparing questions for the community.

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your profile you encourage your readers to respond.

Kind regards,
Anne

[Sandra_Rossi]

Please edit your question (Actions>Edit), select your code and press the button [CODE], which makes the code appear colored/indented, it'll be easier for people to look at it. Thanks!

Also, please use Ctrl+C and Ctrl+V, because you had several typos in your code (doesn't compile).

data

DATA: BEGIN OF it_objk OCCURS 0,
obknr LIKE objk-obknr,
equnr LIKE objk-equnr,
objvw LIKE objk-objvw,
datum LIKE objk-datum,
END OF it_objk.

START-OF-SELECTION

SELECT obknr equnr objvw datum
  FROM objk INTO TABLE it_objk
  up to 1 rows
  WHERE objvw = 'A'
  GROUP BY obknr equnr objvw datum
  ORDER BY datum DESCENDING.
LOOP AT it_objk.
  WRITE: / it_objk-obknr,
  objk-equnr,
  objk-objvw,
  objk-datum.
ENDLOOP.

[Sandra_Rossi]

NB: in SAPquery technology, SQ02 is primarily targeted to infoset, not query.