result in sq02

[Former Member]

Hi all,

I've created some table joins in SQ02, and used such infoset in SQ01.

In SQ02, I have added some codings, but it seems that the coding is effective for each individual field.

How could I get a full picture / whole set of data for the result set and have furter processing?

For example, I want to retrieve the entire result set into internal table for some checking and filtering, what should I do?

Thanks a lot!

[Former Member]

Easie,

?? Your request is unclear. Are you saying that you want to further modify your infoset, such that you create an additional internal table etc etc? Just continue to add more code in SQ02. I am told that any ABAP statements can be added, although I have never done much with this myself. FYI you can review what you have done by looking at the entire abap listing for the resultant ABAP compiled program in SE38. Most queries are stored as an ABAP program which is prefixed with 'AQ'.

If you are, however, saying you want to view the resultant dataset contents, just run SQ01 and list every field in Basic List.

You didn't ask, but I believe you are heading down a deadend street. SQ01/SQ02 are not really designed for complex reports. The more code you add, the more difficult to manage. At some point, you have to ask yourself would you not be better off creating the entire report in ABAP.

Best Regards,

DB49

[Former Member]

ok, let me clarify my request.

I've joined tables MKPF, MSEG, EKPO in SQ02.

Now, on top of user selection in the selection screen in SQ01, it comes out result set R1,

for those records with movement type(MSEG-BWART) = 122 in R1, they should be shown if their

reference document(MSEG-LFBNR) appears as the mat. doc. no.(MSEG-MBLNR) in other records in R1.

How could I do so?

Thanks again!

[Former Member]

Easie,

I have no idea what is the meaning of what you just said. Even in technical terms, the question is not specific enough to generate a logical answer.

Please tell me in business terms what you would like your query to display. Are you trying to create a PO report, that also shows specified movements, or are you trying to create a movement report, that also contains supplementary PO specific data? Or is it a third option? Are you trying to display a report of returns? If so, what business data is required to appear on the return report?

If you must use technical terms, then I need to know the sequence of tables, the fields in each you are using for joins, the fields that are to be available for selection, the fields that are to be displayed, and several examples with sample output rows, that demonstrate the logic of what should be displayed and what should not be displayed.

As an aside, you should be aware that using joins against the material document tables, but selecting on anything other than a keyed field, will usually give rotten performance, unless you have a very strict material document archive policy. MSEG in particular can be a huge table.

Best Regards,

DB49

[Former Member]

Hi DB49,

In business terms, I'd like to retrieve a stock movement report associated to a maintenance work center in a specific period(material doc. posting date). The expected result should contain entries with mainly Goods Receipt (movement type = 101) and some goods return of movement type = 102 or 122 within the selected period.

Now, for those return entries with movement type 102 or 122, they should be shown in the result if and only if their reference document (as movement type 101) is availaible within the same selected period.

Is it more understandable now?

Many Thanks!

[Former Member]

Easie,

This requirement is really torturing SQ01, which is not really designed for this purpose. If a client came to me with this request, I would recommend not to spend the effort to meet all of these requirements within query, I would farm it out to an ABAPer for a custom report.

You can get pretty close to the requirements, but you are talking about two complete reads of MSEG, one or both of them using reads of non-key fields. Most IT managers would skin you alive if you tried to execute this in production. .

Anyhow, here is how I would approach.

In your infoset, Go to the Join area.

create an Alias table such as 'REFMVTS' from MKPF.

Join REFMVTS with MSEG:

Link MSEG-MJAHR to REFMVTS-MJAHR (optional)

Link MSEG-LFBNR to REFMVTS-MBLNR

Use a Left outer join.

Back to the field group area. The only field from REFMVTS you really need (at least to start) is MKPF-MBLNR (posting date). Give it a special label, (such as "RefDocPostingDate"), and special Header name (such as "RefDocPD" so that when you manage this field in the SQ01 query, it is obvious which posting date you are using.

Now, in your query, Do not use RefDocPostingDate in any selection, only in display. Whenever the query finds an entry in MSEG-LFBNR that also exists as MKPF-MBLNR, and the document year is the same, it will display the posting date of the original movement.

Although this does not suppress the data the way you asked, you can instruct the users to ignore any 102/122 entries that have a RefDocPD outside the date range of interest.

Regards,

DB49