topic Re: Selecting from multiple tables, into one internal table in Application Development and Automation Discussions

Selecting from multiple tables, into one internal table

Former Member — Tue, 29 May 2007 22:19:16 GMT

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.

Re: Selecting from multiple tables, into one internal table

Former Member — Tue, 29 May 2007 22:26:39 GMT

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.

Re: Selecting from multiple tables, into one internal table

Former Member — Tue, 29 May 2007 22:33:48 GMT

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.

Re: Selecting from multiple tables, into one internal table

Former Member — Tue, 29 May 2007 22:53:53 GMT

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

Re: Selecting from multiple tables, into one internal table

Former Member — Tue, 29 May 2007 22:58:16 GMT

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

Re: Selecting from multiple tables, into one internal table

Former Member — Wed, 30 May 2007 00:48:53 GMT

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

Re: Selecting from multiple tables, into one internal table

Former Member — Wed, 30 May 2007 02:24:21 GMT

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.

Re: Selecting from multiple tables, into one internal table

Former Member — Wed, 30 May 2007 12:41:16 GMT

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

Re: Selecting from multiple tables, into one internal table

Former Member — Wed, 30 May 2007 16:58:58 GMT

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.

Re: Selecting from multiple tables, into one internal table

Former Member — Thu, 02 May 2019 11:05:23 GMT

Sir,

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

Thanks & regards.