Tool for ABAP Developers: Easy ABAP Open SQL Joins!

 

Warning: Long blog ahead! If you’ve ever written code for a complicated ABAP INNER JOIN, you will not regret the time you will spend here…

Foreword

I prefer the “By a developer, for other developers” approach, to the “By a developer, for himself” approach. Ever noticed how, although it would seem like pure coincidence(or divine intervention!), two or more people manage to find solutions to two separate parts of the same problem, without even collaborating in real time… this is a story of one such experience!

The need

For years now, every time I train developers on ABAP, or mentor them during projects, I have consistently heard quite a few of them complain about how complicated they think it is to construct an Open SQL SELECT statement that’s based on a join on several transparent tables. Of course, there are tools like SAP Query(SQ01) that could help, but to create a query object for just extracting the SELECT statement is overkill. So one day, in mid-2007, I thought: why not create a simple copy-paste program that could automate the whole thing? This tool is borne out of this idea.

The Concept

From the very beginning, I had an eye on contributing this tool and it’s usage to the community… The intended target audience for this tool consists of beginner,  impatient or generally lazy ABAP developers(hmm I think I covered everybody there!) who need to create SELECT statements in code that requires them to join two or more transparent tables. The design goals were:

  1. The tool should allow users to quickly construct INNER JOIN-based queries that could be literally pasted into an ABAP program. It should, therefore, involve as few dialogs as possible and very little in terms of navigational effort.
  2. The developer needs to know the table names, and the order in which they happen to be related and not the exact fields involved in the relationship. The tool should lead to discovery of the exact join conditions.
  3. After the base table, the choice for subsequent tables must be based on existing foreign keys, very similar to Views Transaction SE11
  4. Keep it SIMPLE !

You decide if this program meets its objectives, but then I’m throwing this into the community to be improved/modified/discarded. If you like it, use it… If you don’t, I presume you love ABAP Joins so you can help make it better for the rest of us 😉

The Code

 

How to use this tool

When you run this tool, you will be presented with the following dialog:

Step 1

Click on “Select Base Table”

Step 2

Use the standard table lookup(Personal Value List or Information System) to select your base table

Step 3

Once you select your base table, the interface offers you options to add subsequent tables. I suggest you do this one table at a time…

Step 4

Click on :  Step 5

Step 6

As you keep adding tables, the tool composes the ON conditions based on the foreign key relationships between the chosen table and the previous one. You might want to review the join conditions to ensure everything is in line with your requirements…

Click on: Step 7

Step 8

And if you find something wrong in the process and you wish to delete a table and it’s join conditions, use the delete table function.

Click on: Step 9

Just one more thing to do before we generate the query: Select the fields to be produced from the JOIN.

Click on: Step 10

Click on Add(Button 2 below) to add a field and Add All(Button 1 below) to add all fields from a table.

Step 11

Note: Deviation from standard: To confirm your field list click “Cancel”. Just don’t ask me why 😉

Now we’re almost done, it’s time to pop out a freshly baked SELECT statement:

Click on: 

You should see the output of the generator:

Copy query to the Windows Clipboard… 

Finally, just paste the code into your program!

In closing…

I thought it would be great if one could visualize the output of generated SELECTs, and then, I discovered this tool(Also appears on the SDN download page under WebAS. Look here). It was a pain getting it to work, but once I did, I figured that this is a tool that helps you quickly ALV’ize data output from a JOIN, but you require to type in the query yourself. But what surprised me was how easy it was to generate a query from ZJOINER, paste it into the visualizer tool, make a few changes and while your coffee is still hot, you have a well formed SELECT as well as a visual idea of the data it will produce, with almost no code effort from your side! Now if you consider that these two tools were not originally designed to work together, it is surprising how well they do! I call it inadvertent collaboration!

Anyway, I also think it would be interesting to take this further and do something like what ALVRobot does(also here!) But then, I’m sure if it proves useful, the community will take this further. I intend to post this in the code gallery as well, but I need to explore that area a bit more and learn a few more tricks, hopefully in the next couple of days…

Disclaimer: In the short time I spent testing this tool, I discovered quite a few bugs, which I spent some time solving… I am sure you will too, but I will not be able to spend a lot of time on this tool from now on, I would really appreciate it if somebody could also help fix issues as they are discovered. The program was meant to be a copy-paste solution and that shows in the design.

Also, sometimes, you will need to join tables that are NOT directly related through foreign keys(VBAK & VBAP, for example) and I’m afraid this tool will not help you with this 🙁 unless you do this in a roundabout way.

Credits:

Jayanta Narayan Choudhuri – SQL Tool for ABAP Developers

Gabriel Jenik – The ALV Robot

P.S. I love the WYSIWYG editor !!