Tag Archives: programming

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 !!

ABAP Programming Technique: If you don’t run this report in 10 seconds, it’ll run itself !!!

What is this post about?

As ABAPers, I’m sure all of us have our share of “Eureka!!!” moments, when we discover a possibility that we did not know of earlier…

This is a story of how such a moment left me with a confused set of feelings…
For those interested in the technical details… have you ever wanted to create a report that executes automatically after displaying the selection screen for a specific period of time?
Have you been told it can’t be done?
Surprise… surprise !!! It can ! (And if you already knew, why didn’t you tell me earlier?!)

This post attempts to tell you precisely how to make this possible and, at no extra cost, my personal advice for dealing with the weird feeling of discovering you were wrong about something!

Why a blog?

Having been an SAP Technical trainer for a few years now, I have had the privilege(or discomfort) of being asked questions in and out of ABAP classrooms about things that have nothing to do with the curriculum. Sometimes – I’d like to think “almost always” 😉 – I know the answer, and sometimes I think I know…
In all cases I say what I know, 

“It’s possible to do this, and this is how…” or “I don’t think this is possible… and this is why not…”.

More often than not, the question is answered adequately and everyone lives happily ever after, but once in a while I say, convincingly enough, that something isn’t possible, based on what I believe, what I have seen, or what I have been told…
…only to discover later that I was wrong…

 

 

 

 

 

This is about one of those silly occassions, when – thanks to SDN – I discovered a technique, and before I’m sued for refuting the possibility, I wanted to document it, thus the blog… OK, enough philosophizing, let’s get to work!

The requirement

Recently, in an ABAP class, a curious and enthusiastic student(aren’t they all?) asked me if it was possible toautomatically execute a report after displaying the selection screen for a fixed duration of time… Coincidentally, the same day, I also saw a Need to execute Selection Screen Parameter with time specfic on the same possibility! I have to admit I had considered this possibility earlier and in my younger and more enthusiastic days, I had posed this query to my seniors and peers at the time, only to get the predictable response “Not possible!”
In some time, my research on this tapered off, and I never managed to achieve closure on this issue… Today I know that there are people who know it is possible, but I did not have the fortune of communicating with these Gurus and I assumed, like so many people before me, that it really wasn’t possible, and so I told my student…

But yesterday, when I was foraging through SDN for some juicy blogs, I stumbled across Rich Heilman‘s The specified item was not found. and suddenly(imagine lightbulb above my head and bell sound!), I had the answer to the earlier question…

I always knew about the CL_GUI_TIMER class, but strangely never thought I could use it on a selection screen or without a container, until I saw Rich do it!
Sometimes, a solution is so close, you can’t see it!

image

Obviously, I was wrong… but such discoveries excite me, however uncomfortable the realization! Imagine,(not so)long ago, it was said that cancer was incurable, that there were only 9 planets, that the Sun went around the Earth…
When I thought about the kind of people who said these things, I figured I’m in elite company!

 

 

 

 

 

So for the benefit of those who don’t want to wait years to discover this simple-enough technique, however useless it may seem at first sight, here is the description…

So, How?

For this example, I wrote a simple Executable Program, which has a single field on the selection screen with a default value attached. In 10 seconds, if the user does not execute the program, it executes automatically.
This is achieved by creating an instance of the CL_GUI_TIMER class and setting its interval attribute to 10(or whatever your desired timeout, in seconds) and then invoke the run method. We also need to create a local class to do the job of the handler for the finished event. In my example, the method when_done in class LCL_HANDLER does this. Also remember to register the handler class instance before starting the timer! The only hitch I faced while building this example was to know the Function Code of the “Execute” button, but it didn’t take very long to figure that out…

Here’s the code…

 

This program and any usage of the CL_GUI_TIMER class on an older system (prior to 4.6c) will need you to define a local CL_GUI_TIMER class, look at Rich’s blog linked above to understand how to do this…

And the next time you find something that most people, including you, did not know existed, do everybody a favor and share it with us!

[This is cross-posted from my blog at SAP Developers(or Community) Network athttp://scn.sap.com/people/dushyant.shetty/blog]