Tag Archives: abap

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]

Of NetWeaver and the virtues of bigamy…

If you’re wondering why the topic sounds so out of place, don’t… because I assure you, the meaning will unfold in politically correct ways in the course of this post. Today, with this weblog, I take a very excited first step into two new worlds, NetWeaver and Weblogs, both of which I no doubt feel extremely privileged being a part of. I have been writing technical and non-technical articles for a variety of subjects for some time now, but something about SDN spurred me to jump headlong into it, something similar to what happens when you finally reach an untouched, uninhabited pearl-white beach with heavenly emerald surf after having to go through great pains to discover it. You just have to take a plunge to complete the experience. So here I am… taking that very plunge!

Several years ago, my decision to enter the SAP ABAP and Basis world had just one negative consequence, or so I thought at the time. That consequence was – keeping a long story short – to let go of 4 memorable years of a passionate love affair with a programming language that had grown to become my lifeblood, Java. Even as I type this, I am violently resisting the temptation to write a few decent-sized paragraphs about my adventures with this beautiful, object-oriented, platform-independent, forever evolving language. The reason I resist is simply because I’m aware that this forum is not about programmer’s relationships with programming languages (Somebody I know who’s married to a programmer said to me once, that if somebody took a poll involving programmers seeking to know who they would love to have an affair with, programming languages would top the list!). Well, for me, the transition from Java and J2EE to SAP and ABAP/4 was less painful than I thought it would be, but yes, I missed Java. As I dived deeper into the SAP Application Framework, I was continually overawed by the sheer complexity of the various components and how, they plugged so simply and seamlessly into each other. Soon, I was having a new affair, this time with R/3. After gaining some experience with R/3, I had the opportunity to sit back and summarize the world of R/3, like when you see Earth from space, you may not see the houses and the trees and the people, but in one glance, you grab a snapshot of how everything fits in the complete, broad picture. That was when I realized that there’s something amazing about keeping a monstrous collection of heterogeneous components working together like clockwork across multiple hardware, software and database platforms… across multiple, otherwise incompatible legacy and other third-party applications, programming environments and protocols… across all departments, people and languages of the enterprise… across the world, 24x7x365!

When the impending concept of NetWeaver managed to find it’s way to my observation, the first thing that struck me was the fact that the words “Java” and “ABAP” were coexisting in the same sentence. I smelt opportunity and believe me, it was one hell of a strong scent. I read ahead and was impressed by the possibilities that presented themselves to my imagination. The combination of business level application components built on ABAP or Java using the myriad tools that are and will be part of the framework, and making them work together, providing performance and scalability far beyond what either Java or ABAP would be able to achieve individually, is the kind of stuff that defies mathematics. 1 + 1 in this case is definitely far greater than just 2!

As a result, what I have done in the recent past is: pulled out my Java arrows from my quiver, wiped the dust off them and prepared them to be shot from my SAP R/3 bow, knowing fully well that my armory is now, definitely enhanced. I can almost see the hordes of Java and ABAP fans preparing themselves for what is sure to be the rendezvous of their lives, NetWeaver. Java and ABAP is a combination that’s here to stay. And to everybody intending to be bigamous like I am, enjoy the combination…

I have thoroughly enjoyed reading the Weblogs about NetWeaver and other technologies on SDN and I must say, the webloggers here set great examples and some high standards. I intend to keep this in mind whenever I contribute my bit to the great compendium of knowledge this forum is destined to become. Keep the good stuff coming at SDN and hope you find my posts tolerable!

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