Link MATNR to OBJEK in SQ00 by Recycling an Existing Join
Performing a simple table join between MARC-MATNR and AUSP-OBJEK would be the easiest way to create a report of characteristic values for materials in a specific plant. In SAP, nothing is ever easy.
The ability to link MARA-MATNR and KSSK-OBJEK could be used to create a report of material classifications easily and cheaply. When dealing with a Big 4 consulting firm, nothing is ever simple or cheap.
Consulting firms will inevitably steer customers away from any activity that does not result in billable hours and cite reasons like "because performance issues (sic)". Embedding ABAP code in queries or creating custom views makes linking disparate tables easy, but empowers customers to produce and maintain their own reports using the internal resources who need the reports and understand better than any consultant the requirements thereof.
SAP might already know this trick
In an older system, there is a great chance that somebody has already created a view that has both the MATNR and OBJEK in it. Finding a view where a previous developer has already trained SAP to perform this trick is easier than one might think.
Start in SE16 and view the table DD03VV. This is the table that holds the list of fields in existing views and defines how the view looks.
Search for any view that has a field with the roll type OBJNUM.
From this screen, use the Ctrl-V keyboard combination to start the select process. Highlight the list of views from the TABNAME column and then click Ctrl-C to copy this list into the clipboard.
Open a new SAPGUI window and launch SE16. Paste the list of views into the TABNAME parameter and then type "MATNR" into the FIELDNAME field as shown below.
If there are any views in the system that have a link between MATNR and OBJEK then they will appear in this list. Recycling these views will be much easier (and cheaper) than developing a new view, trying to embed ABAP into a query or writing a new report--all three of which require a developer to prostrate himself before the BASIS team to have transports moved to production not to mention hours spent on producing documentation for functional specifications, documentation for technical specifications and even documentation for testing results.
The final step is to recycle the view in an Infoset using SQ02. One of the two existing views was also linked to AUSP, but linking the material to AUSP when MATNR and OBJEK are joined is a trivial task.
The ugly nature of joins
The good news is that these joins work. The bad news is that these joins work.
When considering the join below, a single material in this client's system might have as many as 19 characteristic values from an AUSP table with 4,000,000 entries. In the image below, one can see that there are eight joins on OBJEK meaning that the query must filter through 198 or 16,983,563,041 value combinations from 8 copies of AUSP or about 65x1051 records and this doesn't count all the iterations of the material in MAKT, MARC and four copies of MARM.
Keep all of this math in mind when producing overly-complex queries. This query was a complete fail as it would never produce any results before timing out and, even if it didn't time out, nobody wants to wait a decade for it to complete.
