Connections between tables

[Former Member]

Hi,

I need to find a way to dynamically include the connections between several tables into a SELECT command. The scenario is:

- A user enters a nr of tables and a fieldlist for each of them.

- I process these in several loops and I build an internal table made up of all these fields (from n different DB tables)

  using the method create_dynamic_table of ABAP class cl_alv_table_create.

- I then have one fieldlist with all fields written in the proper OpenSQL syntax like >KNA1~KUNNR< and so forth.

- I can plug this list into the SELECT command so this part of it is independent of how many fields from how many (and which) tables are in the list.

The catch is: The SELECT command also includes a FROM part which currently reads

FROM KNC1

JOIN KNB1 ON KNB1~KUNNR = KNC1~KUNNR AND KNB1~BUKRS = KNC1~BUKRS

JOIN KNA1 ON KNA1~KUNNR = KNC1~KUNNR

JOIN T001 ON T001~BUKRS = KNB1~BUKRS

I have chosen this example because I knew by heart how to join these tables. I know I can view the

connection between two arbitrary tables (if there is any) via SE11, so i deduce the information must be somewhere.

The task now is finding out where I can get this information.

In the long run then, the user will throw a bunch of tables at me and I have to dynamically implement the connections between them. As an alternative, I can still provide the necessary fields in the dialog and have the user enter the connection details, but I would like to take that hazzle off the user.

Can anybody help me in any way to achieve this?

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi,

I need to find a way to dynamically include the connections between several tables into a SELECT command. The scenario is:

- A user enters a nr of tables and a fieldlist for each of them.

- I process these in several loops and I build an internal table made up of all these fields (from n different DB tables)

  using the method create_dynamic_table of ABAP class cl_alv_table_create.

- I then have one fieldlist with all fields written in the proper OpenSQL syntax like >KNA1~KUNNR< and so forth.

- I can plug this list into the SELECT command so this part of it is independent of how many fields from how many (and which) tables are in the list.

The catch is: The SELECT command also includes a FROM part which currently reads

FROM KNC1

JOIN KNB1 ON KNB1~KUNNR = KNC1~KUNNR AND KNB1~BUKRS = KNC1~BUKRS

JOIN KNA1 ON KNA1~KUNNR = KNC1~KUNNR

JOIN T001 ON T001~BUKRS = KNB1~BUKRS

I have chosen this example because I knew by heart how to join these tables. I know I can view the

connection between two arbitrary tables (if there is any) via SE11, so i deduce the information must be somewhere.

The task now is finding out where I can get this information.

In the long run then, the user will throw a bunch of tables at me and I have to dynamically implement the connections between them. As an alternative, I can still provide the necessary fields in the dialog and have the user enter the connection details, but I would like to take that hazzle off the user.

Can anybody help me in any way to achieve this?

Thanks a lot!

Best regards,

Sapperdapper

[RaymondGiuseppi]

Some of the relations between tables can be found at

- foreign keys relation : DD05P (this one is the most interesting for your examples, will give you links like table1-file1 = table2-field2)

- domain value table : DD01L (ENTITYTAB, look for a field in ENTITYTAB with the same domain)

Regards,

Raymond

[sandeep_katoch]

Hi Friedrich,

Please refer to the below link

http://www.recercat.cat/bitstream/handle/2072/5419/PFCLopezRuizAnnex3.pdf?sequence=4

Hope this helps

Rgds,

Sandeep katoch

[manish_shankar]

Hi Friedrich,

If you want to know how to find relations between tables then this may help you.

1. Go to Transaction SQVI -> Enter the QuickView name -> Create

2.For Data source: Select 'Table Join' from drop down.

3. Click on 'Insert table' and enter the table name.

4. It'll display the relation like this.

- Regards,

Manish Shankar.

[Former Member]

Hi Manish,

many thanks for the answer! Actually, I know how to find out about relations between tables - se11 can display a graphical representation of all relations connecting a given table to the others, so that's easy enough - but I now want to automate the process.

@ Sandeep

I will have a look at that link! Thanks! - Well, actually, that document displays very nicely what are the relations between tables - that is interesting indeed. What I have to try to do now is dynamically generate that info.

@ Raymond

That table looks interesting, I'll have a look at it. Only the word "Some" in your post makes me wonder a bit - well, I have to start somewhere and if I can do 80% automatic, that will probably serve for most situations.

Best regards,

Sapperdapper

[Former Member]

Hi all,

sorry, there was a break inbetween. I was working on something else. Now it's back to this task. I just had a look at that table DD05P. The handling is somewhat confusing - I enter one table, but instead of entering the second table (to which there must be a relation) in the field FORTABLE, I must instead enter it in the field CHECKTABLE. Well, I can work with that. Only, some relations are just not there - among them some pretty "basic" ones, e.g. BKPF-BSEG (header-item) or VBAK-VBAP (header-item) and the like.

I guess I'll have to use something else besides that table - that will be a complex matter.

Best regards,

Sapperdapper

[RaymondGiuseppi]

Missing relations

- BKPF/BSEG, as BSEG is a cluster table, you wont be allowed to JOIN it, did you check the actual database table behind BSEG (RGBLG)

- VBAK/VBAP are not related but they are related to VBUK and VBUP which are related together,

Regards,

Raymond

[Former Member]

Hi Raymond,

I guessed BSEG is a cluster - where do I see that actually? - and no, I haven't checked that yet. I'm not sure if BSEG is a good choice anyway - there's too much in there - and most of it, according to requirement, is in BSID/ BSAD, BSIK/ BSAK and other smaller tables. The ultimate goal is to provide data that will be automatically filled into documents, no more interaction, so it has to be all filtered and such so that only the relevant data is there, so smaller tables (to start with) seem a good idea.

It strikes me as strange that VBAK and VBAP are not related, they are header- and item-table, but then, there are more things strange in SAP... But if they are indirectly related, I should be able to reconstruct that.

Thanks a lot for now!

Best regards,

Sapperdapper

[RaymondGiuseppi]

Hallo Friedrich,

- For BSEG just look at second tab on SE11 (Delivery and Maintenance)

- For VBAK/VBUK there is a reason, SAP tries to nanage a unique key for SD documents, it insure this with tables VBUK/VBUP (which also carry some statuses) as VBAK is not the only header table in SD, e.g. VBRK is also a SD header table.

and now I'll let you work on your requirement

Regards,

Raymond

[Former Member]

Hi Raymond,

being a newbie in ABAP and aware of the fact, I am always grateful for any ideas and comments until I actually close a thread.

I am by now well into a rather complex (multi-layered so to speak) set of nested loops to work on DD05P. I have a question regarding that, however, which might be because I have no idea what relevance the first fields in that table actually have - okay, I can only fill TABNAME in the selection mask, I cannot fill FORTABLE, but afterwards, I only look at FORTABLE (or TABNAME, it's the same seemingly) and CHECKTABLE and the respective keyfields, that's four out of eight fields.

However, there must be a meaning in those fields that I haven't yet understood: When I filter for

>> DDLANGUAGE='DE' AND TABNAME=KNB1 AND CHECKTABLE=KNA1 <<, I get three sets of two keyfields - I need two keyfields to join those tables, but finding six records, four of which seem to be duplicates, makes for further difficulties in that part of the already complex matter.

Can I apply some filter to make sure I get only n records from that table when I have n keyfields?

Thanks a lot!

Best regards,

Sapperdapper

[RaymondGiuseppi]

Sorry for the delay,

- You can filter with one language (like 'DE', or TADIR-MASTERLANG)

- Only look for foreign key relation related to the primary keys, here in KNB1 some non key fields like KNRZB,  KNRZE or REMIT also have a relation to KNA1.

Regards,

Raymond

[Former Member]

Hi Raymond,

that's exactly the point - there are some relations, two between KNB1 and KNA1, on non-key fields which I don't want - but how to filter those out? How can I find out what is the total number of fields making up a unique key? - then I'd simply modify the statement to SELECT only that nr of rows from DD05P. I cannot hard-code it. True, in this small example with four tables, it doesn't hurt to have those surplus relations, but when it comes to 30 tables or so, it might well cause a buffer overflow in my FOR-statement - it all goes into one STRING variable and that will be quite long, in spite of using CONDENSE several times.

Thanks  a lot!

Regards,

Sapperdapper

[RaymondGiuseppi]

There are four relations between KNB1 and KNA1 in my system, as there are 4 "customer number" field in the database table KNB1. So you can choice to only look for primary keys of KNB1, but actualy the user may need the four address fields of the customer. You can identify the primary key fields in DD03L (field KEYFLAG = 'X')

Regards,

Raymond

[Former Member]

Hi Raymond,

so I guess I will have to build in another query of DD03L_int - a much smaller subset of DD03L that I SELECT once and then deal with in my LOOPs, saving me a repetitious full table scan. I will do that. Then it's off to testing - it works fine with the scenarios I can think of, you know, who ever tried to write a foolproof software...

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi Raymond,

I think I have solved this: I use an internal table like always - makes the LOOP execute much quicker - and by joining DD05P and DD03L, I can include a keyflag - relating to FIELDNAME - which I can query in my LOOP so I get only relations using the table_keyfields.

The question is, should I include one more keyflag - relating to CHECKFIELD - to query the field used in the secondary table?

I'm not sure if there is any case where I would otherwise get relations that use the keyfield in the primary, but not in the secondary table?

Do you have some experience in JOINS so you could say whether or not that is possible?

If I cannot exclude that possibility, I have to somehow accommodate it in my code. The issue in that case is, I don't know how - brackets are not possible for some reason, my system doesn't like them, nor is using an OR to link the two sets of keyfields used for the two JOINs. I guess I would have to use two SELECT statements then, which would mean I would end up with two result_sets which I'd have to append if that's possible?

Thanks a lot!

Best regards,

Sapperdapper

P.S.: Ok, I have found a way to include two joins between the same tables in one SELECT statement and I get only 500 records more by joining twice, so I think it's not worthwhile running the risk of not doing it. Now I can be sure that I get only one relation which is using the keyfields of both tables.

[Former Member]

Hi all,

I will now close this thread by marking it "Assumed answered". It does work up to now, using DD05P to fetch the data on relations. I am currently working on code optimization and then I will have to test and re-test on as many scenarios as I can think of - designing a program that someone else will be using always involves the chance of them doing things you didn't think of, more so if the program is supposed to be generic.

Thanks for all the help!

Best regards,

Sapperdapper