Tag Archives: sap

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]

Protecting knowledge or wasting it?

I have spent the last few weeks since my last weblog, with the SAP Community, the online one onhttp://www.sap.com/community . I have been a member of the community for quite some time now, but after I joined SDN, I felt the need for an increased level of involvement with my peers(OK, the reward program at the community and the nifty pen also tempted me, so?). This led me to my sudden deep exploration of the SAP community, more specifically the community forums. I have always felt that a forum always serves as the mouthpiece of a community; A unique kind of democratic environment that allows people observing it to judge, in a few minutes what the entire community is feeling and what it wants to talk about. Though NetWeaver and other core SAP specifics dominate the noise on the forums, what kept me riveted to the forums was the sheer amount of discussion on topics related to SAP Careers.

It is definitely a crowded place out there, with people jostling for attention, topics with very similar titles being posted and reposted till they get worn out and end up inviting replies that contain links to older discussions about the same thing! The signs are clear, more and more people want to be here, now. Why ? Now that is a profound question, and I don’t think I know the complete answer to that, but seeing the crowds at the doors to the SAP world sure does make me feel happier about being part of the SAP community! Gone are the days when somebody asked me what I did for a living, and I desperately looked for quick ways to explain the meaning of ERP and why SAP is actually five words and not three, without looking as uncomfortable as a Windows user on UNIX. For obvious reasons, I left most of those who asked me that question feeling pretty convinced that I was actually unemployed and was making all this up to confuse them. After all, why would somebody be so bad at talking about his own job? I’m so happy that all that is in the past. Now, SAP is the kind of stuff that everybody who has anything to do with any industry loves to talk shop about. This, it can be easily noticed, is reflected in the SAP Forums as well. So where am I leading this to?

My two bit conclusion: SAP is no longer just a representative of one of the many product lines in the ERP segment of a very fragmented Information Technology Industry. Today, as far as careers are concerned, to a lot of people, it means a full-fledged profession.
Picture a group of third-graders talking… First kid : “I wanna be a fireman!” Second kid : “I’ll be a fighter pilot!” Third kid : “I’m gonna be an astronaut!” Fourth kid : “I’m gonna be an SAP Consultant!” … end of discussion. You can’t be much smarter than that, can you?

All right, I plead guilty! That was a case of severe exaggeration… I must have watched too many commercials last night. But anyway, the misplaced point I was trying to make was that more people are seriously planning a move into SAP careers than ever before, and are spending a good deal of time, money and research in an effort to taking the best steps to do it right. And they’re looking for help… unfortunately too many of these people get misled, due to too much incorrect information floating around and too few really experienced people willing to set the record straight. How many of us have seen somebody with several years of manufacturing experience trying to make it as an SAP FIconsultant ? Or a professional accountant whose only exposure to programming has been AUTOEXEC.BAT, trying to be an ABAPer ? In my few years with SAPient Colleges in Asia, I have seen more of these people than I ever expected. It’s not that they never make it. While some of them do, a few among them actually do make the big time. But all of them struggle… the important thing is that they need not, because if the right people had advised them about the things to do(and not to!), they would end up walking through the right doors and soon, somewhere, somehow, make a small but tangible difference to the quality of the SAP world. And who are these right people, you may ask? Slightly rhetorical question, since most readers would know the answer.“Say, Pal! D’ya have a mirror around you?” It may have something to do with how busy we are as members of the SAP community, but it is generally felt that we protect our experiences and our knowledge a lot more in this community than in most other technical and non-technical communities. I remember the hurt I felt when I read a post(not on the SAP Forums, though) that accused senior members of the community of trying to close doors on the faces of greenthumb consultants. So what exactly led to this extremist outburst? A little investigation revealed that the writer in question had posted an innocent, though urgent request for information about a specific R/3 application. Though his question was not exactly rocket science and could have been answered relatively easily, nobody actually bothered to reply, even after he repeated his request several times…

I wish there was some way I could exhort more of the wiser, more experienced members to contribute more to laying a path for newer, inexperienced members of the SAP fraternity (definitely not third-graders, but still not very sure-footed). I don’t know if I’ll succeed, but I’m going to try and take some time out every now and then and dive in the forums doing just that… helping a few newbies make the right moves, helping a few more of the right people come in through the right doors!

P.S.: One of the best things to happen to SDN was the ABAP Programming Forum. I’m going to be there when I don’t have to be anywhere else…

[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]