topic Re: inner join on internal table in Application Development and Automation Discussions

inner join on internal table

former_member549415 — Mon, 28 Apr 2008 11:32:07 GMT

HI all,

How to access data from two internal table using join condition ?

suppose i have tow internal table itab and jtab and i want to access data by using inner join on this tow table.

please tell .

thanx..

Re: inner join on internal table

Former Member — Mon, 28 Apr 2008 11:33:09 GMT

Hi,

Inner join is used to fetch data from two database tables not on internal tables....

use read statement

Re: inner join on internal table

Former Member — Mon, 28 Apr 2008 11:34:44 GMT

hi,

You can use PROVIDE ENDPROVIDE statements in ABAP to achive this.

Check the below documenttaion.

PROVIDE

Syntax

PROVIDE FIELDS {*|{comp1 comp2 ...}}

FROM itab1 INTO wa1 VALID flag1

BOUNDS intliml1 AND intlimu1

[WHERE log_exp1]

FIELDS {*|{comp1 comp2 ...}}

FROM itab2 INTO wa2 VALID flag2

BOUNDS intliml2 AND intlimu2

[WHERE log_exp2]

...

BETWEEN extliml AND extlimu

[INCLUDING GAPS].

...

ENDPROVIDE.

Effect

The statements PROVIDE and ENDPROVIDE define a loop through a statement block. In this loop, any number of internal tables itab1 itab2 ... are processed together. A single table can appear several times. For every table itab you must specify a FIELDS clause. After FIELDS you must specify the character * for all components or a list comp1 comp2 ... for specific components of the relevant table. The names of the components comp1 comp2 ... can only be specified directly.

To be able to process internal tables using PROVIDE, all tables itab1 itab2 ... must be fully typed index tables and contain two special columns that have the same data type (d, i, n, or t) for all relevant tables. For every table you must specify the names intliml1 intliml2 ... and intlimu1 intlimu2 ... of these columns using the addition BOUNDS.

The columns intliml1 intliml2 ... and intlimu1 intlimu2 ... in every row of the relevant internal tables must contain values that can be interpreted as limits of closed intervals. Within a table, the intervals specified in these columns must not overlap and must be sorted in ascending order. The intervals therefore make up a unique key for every row.

For every table you must specify a work area wa1 wa2 ... compatible with the row type and a variable flag1 flag2 ..., for which a character-type data type with length 1 is expected. In the PROVIDE loop, the components specified after FIELDS are filled with values in the relevant work areas wa1 wa2 ... for every specified internal table. The variables flag1 flag2 ... are also filled. A work area wa1 wa2 ... or a variable flag1 flag2 ... cannot be specified more than once.

With the BETWEEN addition you must specify an interval extliml, extlimu. It must be possible to convert the data objects extliml and extlimu into the data types of the respective columns intliml1 intliml2 ... and intlimu1 intlimu2 ... of the individual tables.

The interval limits intliml1 intliml2 ... and intlimu1 intlim2 for every row of all relevant internal tables itab1 itab2 ... that are within the closed interval made up by extliml and extlimu divide the latter into new intervals and every interval limit closes one interval in the original direction. If, within a relevant table, a lower interval limit follows an upper interval limit with no space or gap between them and the components of the corresponding rows specified after FIELDS have the same content, the two intervals are combined and the corresponding interval limits intliml1 intliml2 ... and intlimu1 intlimu2 ... are ignored for the new intervals.

For every interval that is created in such a way and overlaps with at least one of the intervals of a table involved, the PROVIDE loop is passed once. The components of every work area wa1 wa2 ... specified after FIELDS and the variables flag1 flag2 ... are filled with values as follows:

The components intliml1 intliml2 ... and intlimu1 intlimu2 ... of every work area wa1 wa2 ... are filled with the interval limits of the current interval.

If the current interval overlaps with one of the intervals of an involved table, the remaining components of the corresponding work area are assigned the contents of the relevant components of this table row and the variable flag1 flag2 ... is set to the value "X". Otherwise, the work area components and the variables flag1 flag2 ... are set to their Initial value.

Except for intliml1 intliml2 ... and intlimu1 intlimu2 ..., the components not specified after FIELDS are always set to their initial value. The components intliml1 intliml2 ... and intlimu1 intlimu2 ... are always assigned.

The ABAP runtime environment checks for every table involved, whether the condition of sorted and non-overlapping intervals is met within the interval made up by extliml and extlimu and, if necessary, triggers an exception that can be handled.

If the INCLUDING GAPS addition is specified, the system passes the PROVIDE loop for every interval, that is also when the current interval does not overlap with at least one of the intervals of an involved table. In the latter case, the variable flag is of initial value for every relevant table.

You can use the WHERE addition to specify a condition for every table itab1 itab2 ... involved. After WHERE, you can specify any logical expression log_exp1 log_exp2 ... ; the first operand of every comparison must be a component of the internal table. As such, all logical expressions except for IS ASSIGNED, IS REQUESTED, and IS SUPPLIED are possible. You can only specify components that are in the list after FIELDS. Here it is not possible to specify a component using character-type data objects in brackets. The table entries for which the condition is not met are ignored by the PROVIDE loop. You can leave the PROVIDE loop following the instructions in the section Leaving loops.

System fields

The system fields sy-subrc and sy-tabix are set to the value 0 before every loop pass and at ENDPROVIDE. Only if the loop is not passed once, is sy-subrc set to 4 at ENDPROVIDE.

Notes

The relevant internal tables should not be modified in the PROVIDE loop.

The WHERE condition can be used to remove overlaps between the tables involved or to ensure the sorting of the intervals.

In two tables itab1 and itab2, the respective columns col1 and col2 are interval limits of type i. The filling of the internal tables results in the following intervals (rows two and three):

|01|02|03|04|05|06|07|08|09|10|11|12|13|14|

| | Itab2 Int1 | |

| | ... BETWEEN ... |

| | i1 | i2 | i3 | i4 |i5| |

The interval specified in the BETWEEN addition to the PROVIDE statement is shown in the fourth row. It serves as a basis for the five intervals in the fifth row represented by i1 to i5. These can be processed in the PROVIDE loop.

Because each of the five intervals overlaps with one of the intervals from rows two and three, the PROVIDE loop is passed five times.

Only the component col3 of wa1 is filled in the first pass, only the component col3 of wa2 in the third pass, and the components col3 of both work areas in the second and fourth passes. The fields valid1 and valid2 are set accordingly.

DATA: BEGIN OF wa1,

col1 TYPE i,

col2 TYPE i,

col3 TYPE string,

END OF wa1.

DATA: BEGIN OF wa2,

col1 TYPE i,

col2 TYPE i,

col3 TYPE string,

END OF wa2.

DATA: itab1 LIKE STANDARD TABLE OF wa1,

itab2 LIKE STANDARD TABLE OF wa2.

DATA: flag1(1) TYPE c,

flag2(1) TYPE c.

wa1-col1 = 1.

wa1-col2 = 6.

wa1-col3 = 'Itab1 Int1'.

APPEND wa1 TO itab1.

wa1-col1 = 9.

wa1-col2 = 12.

wa1-col3 = 'Itab1 Int2'.

APPEND wa1 TO itab1.

wa2-col1 = 4.

wa2-col2 = 11.

wa2-col3 = 'Itab2 Int1'.

PROVIDE FIELDS col3 FROM itab1 INTO wa1

VALID flag1

BOUNDS col1 AND col2

FIELDS col3 FROM itab2 INTO wa2

VALID flag2

BOUNDS col1 AND col2

BETWEEN 2 AND 14.

WRITE: / wa1-col1, wa1-col2, wa1-col3, flag1.

WRITE: / wa2-col1, wa2-col2, wa2-col3, flag2.

SKIP.

ENDPROVIDE.

The list output is as follows:

2 3 Itab1 Int1 X

2 3

4 6 Itab1 Int1 X

4 6 Itab2 Int1 X

7 8

7 8 Itab2 Int1 X

9 11 Itab1 Int2 X

9 11 Itab2 Int1 X

12 12 Itab1 Int2 X

12 12

Exceptions

Catchable Exceptions

CX_SY_PROVIDE_INTERVAL_OVERLAP

Cause: In one of the involved tables there are overlapping intervals within extlim1 and extlim2.

Runtime Error: UNCAUGHT_EXCEPTION

CX_SY_PROVIDE_TABLE_NOT_SORTED

Cause: One of the involved tables is not sorted in ascending order by the intervals within extlim1 and extlim2.

Runtime Error: UNCAUGHT_EXCEPTION

Edited by: Velangini Showry Maria Kumar Bandanadham on Apr 28, 2008 1:36 PM

Re: inner join on internal table

Former Member — Mon, 28 Apr 2008 11:34:56 GMT

Hi Somnath,

Join is a Part of SELECT statement and SELECT statement works only for Database Tables,

so I don't think so u can use Join on 2 Internal Tables.

Regards,

Sunil

Re: inner join on internal table

Former Member — Mon, 28 Apr 2008 11:36:07 GMT

Ur query should be like this :

select a_{field1 a}field2 b~field1 (and so on..) into corresponding fields of table result_tab

from itab as a inner join jtab as b

on a_{field1 = b}field1.

Here the ON condition will have all those fields which are common to the 2 tables in the query.

But ITAB n JTAB HAVE TO BE DATABASE TABLES, not internal tables.

For internal tables, you ahve to use loop within loop .

Let me know if this is helpful.

Pranjal.

Re: inner join on internal table

Former Member — Mon, 28 Apr 2008 11:38:53 GMT

TYPES:BEGIN OF ST_TABLE,

KUNNR TYPE KNA1-KUNNR,

LAND1 TYPE KNA1-LAND1,

NAME1 TYPE KNA1-NAME1,

ORT01 TYPE KNA1-ORT01,

VBELN TYPE VBAK-VBELN,

ERDAT TYPE VBAK-ERDAT,

ERNAM TYPE VBAK-ERNAM,

END OF ST_TABLE.

DATA:WA_TABLE TYPE ST_TABLE,

IT_TABLE TYPE STANDARD TABLE OF ST_TABLE,

V_KUNNR TYPE KNA1-KUNNR.

START-OF-SELECTION.

SELECT KNA1~KUNNR

KNA1~LAND1

KNA1~NAME1

KNA1~ORT01

VBAK~VBELN

VBAK~ERDAT

VBAK~ERNAM

FROM KNA1 INNER JOIN VBAK ON KNA1_{KUNNR = VBAK}KUNNR

INTO TABLE IT_TABLE

WHERE KNA1~KUNNR IN P_KUNNR.

loop at it_table into wa_table.

write:/ wa_table-kunnr,

wa_table-land1,

wa_table-name1,'

wa_table-ort01,

wa_table-vbeln,

wa_table-erdat,

wa_table-ernam.

endloop.

Edited by: Chaithanya A on Apr 28, 2008 1:40 PM

Re: inner join on internal table

former_member549415 — Mon, 28 Apr 2008 11:44:46 GMT

thanks to all of you for valuable replay