Selecting data with ABAP

[Former Member]

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

[bharat_rathod2]

Dear,

itab01 ,itab02,itab03 are different structure ?

[ThomasZloch]

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

[Former Member]

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

[ThomasZloch]

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

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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

[ThomasZloch]

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

[Former Member]

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

[ThomasZloch]

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

Thomas

[Former Member]

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

[ThomasZloch]

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

Thomas

[Former Member]

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?

[ThomasZloch]

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

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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

[Former Member]

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

[ThomasZloch]

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

[ThomasZloch]

Again, it is easier to help if you provide the code that causes trouble.

Thomas

[Former Member]

Hi Thomas,

how are you? I'm just trying to find my way back to where I was on Friday 😉

As far as I can remember, joining KNA1 and KNB1 did yield the same result when I checked - I will test it again.

The next step is, I must find a way to modify my WRITE statements so I can have the "column headers" on top of my list so I know what data represents what DB_table field, then I can check again whether the tables are correctly joined.

Talk to you soon,

Sapperdapper

P.S.: There seems to be something wrong with the output - the "check fields" seem to not be there and a lot of data seems to not be filled in every line, but I cannot tell for sure, so before I can formulate more precise questions I have to get the alignment right. I can post my code for you no problem, it's only an example as yet - I have it in several pieces, I have used an INCLUDE for the declarations, one for the actual SELECT and one for the WRITE command generating my screen list.

[Former Member]

Hi Thomas,

I also have to consider the next step, parallel to getting the screen list right, since the screen list is only a temporarily needed tool for checking results.

The next step is having the data from my internal table output in XML format. I know that's possible in ABAP and I have already found the right tool, the command CALL TRANSFORMATION. However, the SAP_help online is difficult when it comes to finding out the right syntax. I think the standard transformation ID might be what I need since I don't want to do anything fancy, just get all the data I have in my internal table into an XML document, field by field and record by record.

Can you help me in any way with that?

Thanks a lot!

Best regards,

Sapperdapper

[ThomasZloch]

Sorry, can't replicate all what the help files have to offer. Instead of WRITE statement, better use ALV technique provided by classes CL_SALV_TABLE and related (many examples available). For XML generation, use "simple transformations" with statement CALL TRANSFORMATION, as you have found out already. Covered by SAP Help, and there are some good books available via SAPPress.

Thomas

[Former Member]

Hi Thomas,

ok, I understand. I cannot expect you to know everything, SAP is too big for that. Maybe I will find some help elsewhere.

Yes, there are some good books on the issue and I have already bought one.

It's also covered by the SAP online-help, only that is quite difficult to read for someone not familiar with the entire ABAP environment. Sometimes, it seems to describe things in a circular way.

I understand that the WRITE statement is not the ideal way to generate my screen list, but since the screen list is only a temporarily needed tool for checking the results of my data query, I will not invest time in perfecting that unless it proves really necessary.

I guess I will make it somehow.

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi Thomas,

I am stuck: I'm just trying to enhance my ABAP program to encompass four tables: KNC1, KNB1, KNA1 and T001.

I will attach the INCLUDE structure containing my SELECT statement.

I cannot see anything wrong about it, but SAP is telling me that "the command ending on "KNC1~KUNNR" is unexpectedly closed" and it points to the line immediately following the SELECT keyword.

Can you tell me what is wrong with my code?

Thanks a lot!

Best regards,

Sapperdapper

[ThomasZloch]

Omit the colon after SELECT and the commas between the column names. Always consult ABAP online help first, there is some examples that clearly show how to properly write a SELECT statement. I gave one in this thread here as well.

Thomas

[Former Member]

Ah - well, doing it just like I have it in my last report (joining just three tables) will probably fix that - taking a look before would have helped

I will always try to fix my problems using the online help going forward.

Thanks anyway!

Best regards,

Sapperdapper

[Former Member]

Hi Thomas,

ok, I have just checked that joining KNA1 and KNB1 in either direction really does not make a difference. That is new to me, but it does make the next step a lot easier, trying to use variables for the fields and then the tables. Needless to say, my program with four tables works now 😉

Ultimately, it will be the user's free choice which - and how many - tables and fields they want to join, so I have to develop a way to dynamically assemble the code.

Talk to you,

Sapperdapper

[Former Member]

Hi Thomas,

I am just experimenting with subroutines as opposed to INCLUDE structures - subroutines have a data link, so I can hand down variables that the user provides in a selection screen.

I have declared seven variables for the fields I want from four different tables and handed down those variables into a subroutine  (using USING) doing the declarations for the internal table.

When I test that for syntax errors, SAP always tests the entire program. Further on in that program, there is an INCLUDE structure working through my internal table "itab01" using LOOP AT and generating the screen list with a WRITE command - it is there that I get a syntax error "field itab01 unknown".

I have looked up the help and found out I need to use the option CHANGING for any parameters I want to hand "up" from the subprogram to my main program.

The problem now is: That internal table is declared using a DATA statement in the subprogram, so it does not exist before - but that does not seem to be working: I now wrote FORM...USING... CHANGING... at the beginning of my subprogram (USING for the seven fieldname-variables I have declared in the main program for now and CHANGING for the internal_table itab01). Still, in the INCLUDE doing the WRITE further on, I get the message "field itab01 unknown".

Can you help me out there?

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi,

I have been told elsewhere that I can use variables in INCLUDE structures just as well - I will try, but if that works, I would rather stick with INCLUDE structures. Having several separate pieces of code makes the whole thing somehow more orderly and easier for an overview - I won't have to face the scenario of another program trying to use these, so that problem won't pop up.

Best regards,

Sapperdapper