Selecting from multiple tables, into one internal table

[Former Member]

Hi,

What is the best & most efficient method of selecting from multiple table (in my case 6,) into one internal table?

Thanks,

John

• Points will be rewarded and all responses will be highly appreciated.

[Former Member]

Hi,

What is the best & most efficient method of selecting from multiple table (in my case 6,) into one internal table?

Thanks,

John

• Points will be rewarded and all responses will be highly appreciated.

[Former Member]

Use Inner join for 3 tables to get better performance ,always use for all entries with primary key condition.

Inner join - there no limit and you can join n number of tables.

[Former Member]

I have simple example :

• First one - Join 5 tables

data : f1 type i,

f2 type i,

f3 type i.

start-of-selection.

get run time field f1.

• write the query 4 or 5 tables join.

get run time field f2.

f3 = f2 - f1 ( Total time).

• Second one - joins 3 table and use for all entries

data : f1 type i,

f2 type i,

f3 type i.

start-of-selection.

get run time field f1.

• write the query 3 tables join and use for all entries

get run time field f2.

f3 = f2 - f1. ( Total time )

Finally you can have time diffrence between the both sql statement.

[Former Member]

Can you please specify the table names here?

Becuase the most efficient way to fetch data from so many tables is Logical database, if they are in logical hierarchy.

You can view logical databases using SE36

[Former Member]

Well, the tables are used for getting all PERNR numbers, which are entered into different tables. The tables are A867, A865, A830, A864, A835, A869

[Former Member]

Hi John,

These are Tax Condition tables and they have almost same structures.

In this case, you can write multiple queries and accumulate data into single internal table.

DATA : BEGIN OF ITAB,

KAPPL LIKE A867-KAPPL,

KSCHL LIKE A867-KSCHL,

OIC_OREGIO LIKE A867-OIC_OREGIO,

END OF ITAB.

SELECT KAPPL KSCHL OIC_OREGIO

<b>APPENDING</b> CORRESPONDING FIELDS OF TABLE ITAB

FROM A867 WHERE ...

SELECT KAPPL KSCHL OIC_OREGIO

<b>APPENDING</b> CORRESPONDING FIELDS OF TABLE ITAB

FROM A865 WHERE ...

SELECT KAPPL KSCHL OIC_OREGIO

APPENDING CORRESPONDING FIELDS OF TABLE ITAB

FROM A830 WHERE ...

And so on.

APPENDING clause will append the data into internal table and it will not remove the existing data.

CORRESPONDING FIELDS OF TABLE ITAB will move data to matching fields of internal table. It will move data whose name is matching with the table field name. For ex, KSCHL of table will be moved to KSCHL of internal table.

[Former Member]

Hi John,

When you need to select from more than one table, you are left with only two options

1. use Join.

2. Select data in one internal table and based on that select into another table and so on. At the End you need to consolidate entried in one internal table or output table using LOOP and READ statement.

The decision is generally depends upon the table in question, the no. of entries expected and values(keys) available for selection.

Hope it is clear. Revert back if further query.

Reward points if useful.

Regards,

Atish

[Former Member]

Hi John,

The main thing which you need to consider are:

1. The number of entries in each table

2. Whether any of the tables from which data needs to be fetched, is a cluster or pool table. In case of cluster table, you can not use JOINS. In this case, you would have to select into individual tables and then LOOP to build a single table.

Hope this helps.

Regards,

Bharati

[Former Member]

Sir,

please tell me step by step how to join bkpf and bseg tables using select.

Thanks & regards.

[Former Member]

Hi,

If u want to join multiple tables then go for Inner Join thts the better option for u.

That is using select statement.They r other ways also but this is much useful one.

if useful reward with points,

with regards,

madhuri.