SELECT with JOIN from internal tables?

[Former Member]

Hi,

I have a code that works on a hard-coded list of tables - all the tables relevant for, say, the creation of a purchase_order. The code then queries several DD tables: The table_texts from DD02T, the fields from DD03L and the field_texts from DD03T.

There is a very annoying issue, that is that some tables, like BKPF, just don't exist in DD03T. The fields which are in BKPF, e.g. BUDAT, are there all right, several times in several tables with the texts being different - so I cannot be sure which would be the correct text and I cannot properly link the table to DD03L.

That is not, however, my principal issue right now and I have asked that question elsewhere. My current issue is: Those DD tables are huge and I have a LOOP - to grab the hard-coded table_names which serve as search_criteria for texts and fields. Doing a complete table scan with every loop execution (currently 14, will become more) takes too long. Therefore what I'd like to do is SELECT a subset from each of those tables (maybe filtered for my language and the length of table_names so I don't have to bother with all those cryptical internal tables) into an internal table - that would be necessary just once - and then use just the internal tables within the loop.

The issue is, on internal tables I cannot do a SELECT command including JOINs between several tables, or can I?

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

hi Sapperdapper,

To do it first wee need to have a Fields mapping between the internal table and Check the fields on which your requirements plays a Lot.else please provide internal table structures so that we will try to do it.

[Former Member]

Hi,

What I Understood is U have Some internal tables out of which u need to select the data.

Sorry if I am Wrong.

If you want to select the data between the internal tables Use Parallel loops with Where condition to Filter the data and Keep in a Single final internal table.

[Former Member]

Hi Rajesh,

no, you are quite right. I want to effect the equivalent of a SELECT with several JOINed DB tables, only on internal tables that I have already prepared.

OK, I know how to do a LOOP AT with internal tables, but only on one up to now - but how do I do that with several internal tables, some information from which I want to join in one single internal table?

Thanks a lot!

Best regards,

Sapperdapper

P.S.: I just found another thread here to the effect that I can LOOP AT from several tables and concatenate - but that does not establish a logical relation, does it? I cannot just take the first record from table A and the first record from table B and concatenate them if they don't match, I will get garbage...

[Former Member]

hi Sapperdapper,

To do it first wee need to have a Fields mapping between the internal table and Check the fields on which your requirements plays a Lot.else please provide internal table structures so that we will try to do it.

[Former Member]

Hi Rajesh,

I suppose by fields_mapping you mean the keyfields?

I think I understand what you mean, I will try it out - I need to LOOP through the first of those tables and within that LOOP, open a LOOP through the second table and (parallel) one through the third table, use WHERE conditions in the inner LOOPS using as arguments the keyfields from the outer LOOP. We'll see.

The internal table structures are not complicated - I need tables DD02T (table_texts), DD03L (fields) and DD03T (field_texts). My internal tables are like this:

TYPES: BEGIN OF DD02T_int_line,

       TABNAME TYPE C LENGTH 10,

       DDTEXT TYPE STRING,

       END OF DD02T_int_line.

TYPES DD02T_int_type TYPE STANDARD TABLE OF DD02T_int_line.

DATA DD02T_int TYPE DD02T_int_type.

DATA wa_DD02T_int TYPE DD02T_int_line.

TYPES: BEGIN OF DD03T_int_line,

       FIELDNAME TYPE STRING,

       DDTEXT TYPE STRING,

       END OF DD03T_int_line.

TYPES DD03T_int_type TYPE STANDARD TABLE OF DD03T_int_line.

DATA DD03T_int TYPE DD03T_int_type.

DATA wa_DD03T_int TYPE DD03T_int_line.

TYPES: BEGIN OF DD03L_int_line,

       FIELDNAME TYPE STRING,

       END OF DD03L_int_line.

TYPES DD03L_int_type TYPE STANDARD TABLE OF DD03L_int_line.

DATA DD03L_int TYPE DD03L_int_type.

DATA wa_DD03L_int TYPE DD03L_int_line.

In the following, I have (up to now) a SELECT command with a JOIN between those - that used to work on the DB tables, but that was too slow and now of course it doesn't work anymore that way.

An issue playing into this is that I cannot find all the table_names in DD03T, so I cannot join on TABNAME, but only on FIELDNAME and I somehow have to boil it down to only one text - for ex., I cannot find BKPF in DD03T. BELNR, which is a field in BKPF, is there 3 times (in German) with 3 different texts, but I can use only one. Right now, I select all 3 and delete the duplicates afterwards, but it would of course be better to just not select more than one - which isn't that easy because there are 48 hits (with language German) and the first one has no text, but there are 3 with texts.

Thanks a lot!

Best regards,

Sapperdapper

P.S.: Sorry for the long answer, but this is really blocking my progress right now - I admit I just don't know how to do it, so there is nothing I can do - save of course trying out things I read somewhere or what you tell me.

[Former Member]

Hi,

With reference to our discussion i would like to suggest one FM which will help you to get the Text of the each Filed.

RFIDPT_GET_FIELDNAME_TEXT.Please check some more Fm are available in the standard.

Keep an update for help

[Former Member]

Hi Rajesh,

if you don't mind, I'd rather stick to the methods I know for the moment rather than try using a novel method which I have to learn first - moreover, I've been told that FM (well, a very similar one for table_texts) can only process one field/ table at a time, so I wonder if it would really be any faster.

I have developed an alternative code that seems to be running faster by a factor 5 or so by selecting subsets of those DD tables first and then working on those.

The question is, is this maxed out or can I somehow make it run even faster?

I'll post the code such as it is now here - so first I do the SELECTs from the DD tables to get the much smaller internal tables and then I do this:

LOOP AT Tables_tab INTO wa_tables.

   CurrTab = wa_Tables-TABNAME.

     READ TABLE DD02T_int INTO wa_DD02T_int

          WITH KEY TABNAME = wa_Tables-TABNAME.

          wa_tabs_total-TABNM = wa_Tables-TABNAME.

          wa_tabs_total-TABTXT = wa_DD02T_int-DDTEXT.

          APPEND wa_tabs_total TO Tabs_total.

     LOOP AT DD03L_int INTO wa_DD03L_int

          WHERE TABNAME = wa_Tables-TABNAME.

       READ TABLE DD03T_int INTO wa_DD03T_int

         WITH KEY FIELDNAME = wa_DD03L_int-FIELDNAME.

          wa_fields_total-TABNM = wa_Tables-TABNAME.

          wa_fields_total-TABTXT = wa_DD02T_int-DDTEXT.

          wa_fields_total-FLDNM = wa_DD03L_int-FIELDNAME.

          wa_fields_total-FLDTXT = wa_DD03T_int-DDTEXT.

          APPEND wa_fields_total TO Fields_total.

     ENDLOOP.

ENDLOOP.

This one takes about 9.42sec for 14 tables (approx. 1700 fields). Much faster than before, but when it gets to 50 tables or more, it won't take 5 times that, but it's still too long. Can I do any better by modifying my code?

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi Sapperdapper,

Its is Better way . Use sy-subrc check after the Read statements to avoid the unnecessary append.

Best Regards,

Rajesh

[Former Member]

Hi,

SELECT...INNER JOIN statement cannot be use on internal table. If you really wish to use it to reduce complexity of your program, maybe you can try to declare some Z tables and then insert your internal table to it, after that you can use SELECT..INNER JOIN to retrieve the data based on your requirement. It might cause some performance issue (depending how big is your "subset" of data), so use it wisely.

Regards,

Xavier

[che_eky]

You seem to be doing it the hard way, getting bogged down with the detail. There are lots of standard functions that will give you most if not all of the information you need.

Function RS_DS_INT_TAB_INFO gives you table text and field texts.

Function DDIF_NAMETAB_GET gives you more detailed field information.

By all means carry on as you have started but you seem to be reinventing the wheel.

Che

[Former Member]

Hi,

well, I didn't know of those functions. Rather than open the function builder and search through a list of some hundreds of functions and modules in SAP, I can write the same functionality faster - what runs faster is another question. I will naturally, going forward, have a look at all of these (all I can find, that is...) in search of anything that will make my code faster.

Best regards,

Sapperdapper

@ Rajesh

What do you mean by checking sy-subrc? I know that >sy-subrc< is where I will get a code if an error occurs during any operation, but how can that help me to streamline my code? Avoiding an APPEND from the WA seems a good idea, but I don't understand.

[Former Member]

Hi

Below is some of your COde

LOOP AT Tables_tab INTO wa_tables.

   CurrTab = wa_Tables-TABNAME.

     READ TABLE DD02T_int INTO wa_DD02T_int

          WITH KEY TABNAME = wa_Tables-TABNAME.

if sy-subrc = 0.  " Try to add this Sy-subrc check

          wa_tabs_total-TABNM = wa_Tables-TABNAME.

          wa_tabs_total-TABTXT = wa_DD02T_int-DDTEXT.

          APPEND wa_tabs_total TO Tabs_total.

Endif. " Try to add this

If you dont use this check, The internal table will have inconsistent data.

[Former Member]

Hi Rajesh,

I don't quite understand what would happen and why without this, but adding that won't be a problem.

[Former Member]

Suppose

LOOP AT Tables_tab INTO wa_tables.

   CurrTab = wa_Tables-TABNAME.

     READ TABLE DD02T_int INTO wa_DD02T_int

          WITH KEY TABNAME = wa_Tables-TABNAME.

*& if you dont find relavent data in  DD02T_int what should happen?

*& It should not append to Tabs_total internal table right?

*& in order to make a check if the data in the internal table exits the Value of the Sy-subrc = 0.

*&Hence we include Sy-subrc like

if sy-subrc = 0.  " Try to add this Sy-subrc check

          wa_tabs_total-TABNM = wa_Tables-TABNAME.

          wa_tabs_total-TABTXT = wa_DD02T_int-DDTEXT.

          APPEND wa_tabs_total TO Tabs_total.

endif. "Try to add this Sy-subrc check

[Former Member]

Hi Rajesh,

I tzhink now I understand - but, yes, if I cannot find relevant data in DD02T - that is, if there is no text on my current table - I still want to APPEND the table_name to Tabs_total. You see, Tabs_total is my final table and it is crucial that it should contain all the tables in my "starting-list" Tables_tab. Having a text for every table is of course preferable as it makes it easier for the user, but in case there is no text, I still need the table. It is the same with fields - if there is no text, I still need the field.

I'm not sure now whether you misunderstood me before or I misunderstood you now...

Thanks a lot either way!

Best regards,

Sapperdapper