topic Re: Selecting data with ABAP in Application Development and Automation Discussions

Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 06:49:19 GMT

Hi,

I am just trying my first steps with ABAP - I have a task, the development of a concept for a new solution, the actual programming can be done by more experienced colleagues, but I won't be able to do without programming at all just to test out what is possible and to develop a prototype.

So, the scenario is this: I want to select data from KNB1 (customer masterdata, cc-specific) and add to that some info from KNA1 (customer masterdata global) => For every record I have for one specific customer in KNB1, I want e.g. the name and street from KNA1 in the table.

In another forum here in the SDN, I have been told a possible way to do that:

- I prepare an internal table itab01 with all the fields I want from KNB1.

- I prepare an internal table itab02 with all the fields I want from KNA1.

- I prepare an internal table itab03 with all the fields from both tables.

- I fill the data from KNB1 into itab01 using SELECT.

- I fill the data from KNA1 into itab02 using the >>FOR ALL ENTRIES<< clause.

- I use >MOVE-CORRESPONDING< to move both the data from itab01 and the data from itab02 into itab03.

My question: Is there a quicker way to get the data from itab01 and itab02 to itab03 - quicker than using >>LOOP AT<< and reading every record in a workarea, transferring it to another workarea and appending it to another internal table? I have tried an array-fetch, but I got a syntax error because my internal tables are of course no ABAP dictionary objects.

Thanks a lot!

Best regards,

Sapperdapper

Re: Selecting data with ABAP

bharat_rathod2 — Fri, 31 Aug 2012 07:39:28 GMT

Dear,

itab01 ,itab02,itab03 are different structure ?

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 08:36:54 GMT

The best way for your scenario is to just work with one ITAB with the required fields from both tables and select all data from both KNB1 and KNA1 in one go using a SELECT with addition JOIN. The connection between both tables is field KUNNR.

This way you don't need to worry about merging several internal tables into one.

Please read ABAP documentation for JOINs and search for examples, there are plenty of.

If you read about "don't use joins" or "don't use INTO-CORRESPONDING" somewhere, then ignore these and all other posts from the respective authors.

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 10:00:33 GMT

Hi,

@ Bharat:

Yes, right now itab01, itab02 and itab03 have a different structure. All are internal tables, but the line_type is different: itab01 has lines containing the fields I want from KNB1, itab02 has lines containing the fields I want from KNA1 and itab03 has lines containing all fields.

@ Thomas:

Working with just one internal table instead of three is a good proposal, I had the same idea to leave as few traces as possible for reasons of data_security as well as performance. I'm unsure about the Join, but honestly I haven't yet tried it out. I had read so much about >Joins vs. FAE< and the like, I just posted my scenario in another place here and someone proposed me that code structure using FAE to reconstruct the n:1 relation and get data in all records/ lines. The thing with Joins is I have been told that using Joins between >3 tables is discouraged, but I have to develop a possibility to m.o.l. allow for any number of tables - well, not any number, but more than three. Still, I will try out your proposal. I can still do a Join with two tables, feed the result into an internal table and join again.

I have realized there is plenty of documentation on all aspects of ABAP development online.

I have a few books on the issue, too, and I will continue using both my books and whatever I can find online.

I find it difficult, however, to go with ignoring some part or other of whatever_documentation_I_find. I am responsible for my solution, I cannot delegate responsibility, so I have to try all possibilities I can think of to decide what works best.

Whatever - my question is still the same. I am now running my little program in debugging-mode to make sure that itab01 and itab02 are properly filled with data and I can apply filters on the DB tables.

Then I have to loop through itab01 and itab02 respectively and use MOVE-CORRESPONDING to transfer data line-wise from the internal table to its workarea to the next workarea to the next internal table. Can I do that quicker, block-wise instead of line-wise?

Thanks a lot!

Best regards,

Sapperdapper

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 10:20:55 GMT

I won't help further regarding the FAE and multiple itab approach, because it's so much worse.

You can join up to 25 tables as a technical limit, in practice I have worked with 7 or 8 tables without problems, provided the ON-conditions are constructed properly.

Here is a recent discussion that sums it up from my point of view:

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 10:33:31 GMT

Hi Thomas,

well, it's your free decision to help only on the approaches you think will work best.

I would very much like to do with only one intTab instead of three if I can. The less ado I have with the data, the faster the solution will be and the less I have to worry about sensitive data being shoved around. I am more than ever confused about Joins: In another forum here in the SDN I have been told three is the limit of what makes sense, now you say 25. I guess I will try what makes more sense performance-wise.

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 10:39:30 GMT

25 is the technical limit (see ABAP documentation), in my practical experience I have worked with up to 8, if I remember correctly.

I can only warn again, do not believe most of the "urban legends" that are going around, like "don't use into-corresponding", "use FAE instead of joins", "use parallel cursor method", etc. Most of this stuff has been copy/pasted again and again for many years.

If in doubt, post a link here and we'll have a look.

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 10:44:59 GMT

Hi,

well, I will try. I can look up the syntax for a Join no problem. The important thing is that I get the data the way I want it. Keeping with the example, for every one of the n records for any given customer in KNB1, I want the info from KNA1.

Best regards,

Sapperdapper

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 10:51:19 GMT

Sounds good. Here is a snippet to get you started (based on the link I gave above):

SELECT

knb1~kunnr
knb1~bukrs
kna1~name1
...
FROM knb1
JOIN kna1 ON kna1~kunnr = knb1~kunnr
INTO CORRESPONDING FIELDS OF TABLE it_final
WHERE knb1~bukrs EQ p_bukrs
AND knb1~kunnr IN s_kunnr

...

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 11:33:40 GMT

Thanks!

I take it p_bukrs and s_kunnr in your code-snippet are user-parameters coming from a dialog? Well, I don't have one for the moment - to begin, I will have everything hard-coded, the next step is building a dialog to see how far I can get by using variables - I would like to be able to m.o.l. arbitrarily pick two tables and combine (join) them dynamically.

In the end, the whole program will be ímplemented in a function module and will be called from external using the SAP JavaConnector for RFC.

Best regards,

Sapperdapper

P.S.: I hope this is not too simplistic a question: What does it mean when I constructed my SELECT (not a loop when I use an array-fetch, so I need no ENDSELECT) and the first field I specified was KNB1-BUKRS (with a comma because I constructed the whole as a chain-kit) and I get a syntax error with the message (pointing to that exact line): "The sentence ending on KNB1-BUKRS was unexpectedly closed" - the sentence is not supposed to be closed there. the "full stop" is at the very end of the entire command, right where it should be...

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 12:14:17 GMT

In such cases it is best if you post your actual code here (only the relevant part, of course).

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 12:52:32 GMT

Hi,

the relevant part of the code (after all the necessary declarations) is the following:

SELECT

KNB1-BUKRS

KNB1-KUNNR

KNA1-KUNNR

KNA1-NAME1

KNA1-NAME2

KNA1-STRAS

KNA1-PSTLZ

KNA1-ORT01

FROM KNB1 JOIN KNA1 ON KNA1-KUNNR = KNB1-KUNNR

INTO CORRESPONDING FIELDS OF TABLE itab01.

I tried to construct this SELECT-statement as a chain-kit, but that did not work - that was the syntax error I mentioned above - and now the syntax error tells me alternately that "KNA1-KUNNR is unknown" or "KNB1-KUNNR is unknown", none of which I can understand.

Thanks a lot!

Best regards,

Sapperdapper

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 13:15:27 GMT

Please use the tilde "~" instead of "-" to separate table and column names, just as I posted above.

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 13:21:34 GMT

Perfect! It works.

However, I am confused now: I have the book "Discover ABAP" which I have read and done all the exercises and they all worked using the dash "-". What is different now?

Best regards,

Sapperdapper

P.S.: Not quite - it does not give me all the data: When I have a look into KNB1 via SE16, I see that one specific customer is there twice, in CC 1000 and 3000 - I would expect to see two records in my result list, each with the name and personal data from KNA1. With the standard JOIN, I get only one. The different OUTER variations, afaIk, give me the records where no matching entry in the other table is found, but that's not what I want: The way I understand it, there are two records in KNB1 and for each of them, there is a match, both times the same record from KNA1 matches - but I want to see the two.

What am I doing wrong?

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 13:50:26 GMT

In short, you always use the dash to separate table and field names in ABAP statements, except when joining multiple tables in an SQL statement, there you need to use the tilde (column separator). It is only mandatory if there are columns with identical names in more than one table, but for better readability of the code I would always include the table name.

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 13:55:02 GMT

Okay.

The error I just told you about in my latest edit doesn't exist - I fooled myself, I had put in a filter for only one CC and not remembered it...

Thanks a lot!

One more question: When I join more than two tables - imagine I have KNB1 (my n table, with n records for a customer) and I want to join it with KNA1 (1 record for a customer) and with T001 (1 record for a CC). Do I need to follow a specific order in which I mention the tables in my ABAP statement? My long-term goal is to make it all as flexible and dynamic as possible, so that is very important.

Best regards,

Sapperdapper

P.S.: Readability is very important also, I am with you there. I cannot count the times I was brooding over a script written by someone else - without any comments in the code - and I didn't understand what it was supposed to do, so I couldn't even think of deciding whether it was doing what it was supposed to...

I am trying to make my code as readable and intelligible as possible from the start. I think one line of comment every few lines of code is not too much. I will avoid technically working abbreviations of commands and the like and make it all as obvious and explicit as possible.

Re: Selecting data with ABAP

ThomasZloch — Fri, 31 Aug 2012 14:17:50 GMT

Good question regarding the order of the tables. I would start with the table that probably has the smallest result set based on the WHERE conditions. However, the CBO (cost based optimizer) of the database will usually choose the best access path based on circumstances (e.g. select options filled) and might change the sequence at runtime.

Joining T001 is not a problem, the link is KNB1~BUKRS = T001~BUKRS, you might have guessed this as well.

Thomas

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 14:27:27 GMT

Hi,

I did guess - know - that, I have worked with these tables many times, that's why I chose those as my example.

I asked because in my old company, we worked with an external analysis program called ACL on SAP data - and there the order of the tables in a join command mattered very much:

Writing >> JOIN ... KNB1 WITH KNA1 << worked while by writing >>JOIN KNA1 WITH KNB1<<, you would lose a lot of data because you'd get only one record per customer as is the case in KNA1 while in KNB1 you can of course have n records per customer.

No worries, I can always test it. Now I know the basic pattern and I can easily enlarge the code to allow for more tables.

Best regards,

Sapperdapper

Re: Selecting data with ABAP

Former Member — Fri, 31 Aug 2012 14:50:38 GMT

Hi,

now I am trying the same thing with three tables and get a stupid - probably very simple - syntax error: I have declared the type of the lines for the internal table with all the fields I want, then the type for the internal table etc. - and now SAP is telling me that the workarea of my internal table does not contain an element "BUTXT" - the text from T001 that I want - but I have definitely included that in the line type.

Can you help me there?

Thanks a lot!

Best regards,

Sapperdapper

P.S.: OK, it works in spite of that - I can still activate all the elements and it runs fine - only the linking does not seem to be correct: I have included in the list both the BUKRS from KNB1 and the BUKRS from T001 - and it's not the same...

Re: Selecting data with ABAP

ThomasZloch — Sat, 01 Sep 2012 14:39:43 GMT

Actually, inner joining "KNB1 then KNA1" and "KNA1 then KNB1" should yield the same result set, provided the ON- and WHERE-conditions are the same as well. Please doublecheck.

Thomas