Inner Join with Internal Table

[Former Member]

Hi Abapers,

can we have the Join (Inner Join) between database table and internal table? this the scenario:

Internal table A (ITA)

rsnum material

1111 xxxx

2222 yyyy

3333 zzzz

Database Table B (DB)

rsnum qty

1111 3

2222 4

4444 7

6666 3

Expected Final Answer - internal table B (ITB)

rsnum material qty

1111 xxxx 3

2222 yyyy 4

can we something like this:

select *

from DB

into coressponding table of ITB

Inner join ITA on DB_{rsnum = ITA}rsnum

where DB~qty > 0.

[Former Member]

Answer is no,we cant put Join DB and Internal Table.

But You can join on number of DB Tables.

Small Tip: Rather than Filing ITAB, you can fire JOIN Directly on DB tables fo fill Final Internal Table.

[Former Member]

since both database and internal table CAN'T put in JOIN condition,

internal table A - ITA : this data is selected between current month and year

rsnum currentdate(month/year)

1111 200808

2222 200808

4444 200808

database table B - DbB - this data show all data from previous and current

rsnum previous date(month/year)

1111 2008080

2222 200801

3333 200707

4444 200309

expected answer - ITC - show the data if the current date(ITA) = previous date (DbB)

rsnum date

2222 200801

from this example , that's why i'm try to implement the inner join between ITA and DbB.

anyelse suggestion ?

[Former Member]

hello hashim,

you can use for all entries option in select query.

select rsnum qty from <DB_table_B> into table ITA_C

for all entries in ITA_A

where rsnum = ITA_A-rsnum.

after that you can loop at internal table A or C and append the result into tbale B.

with regards,

sandeep akella.

[Former Member]

Hi.

You can join database table and internal table using 'FOR ALL ENTRIES IN itab WHERE cond'.

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

END OF ftab_type.

DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH

NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

free TYPE I,

wa_sflight TYPE sflight.

- Suppose FTAB is filled as follows: 
- 
- CARRID  CONNID 
- -------------- 
- LH      2415 
- SQ      0026 
- LH      0400

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

Best Regards

Kyung Woo

[Former Member]

Hi,

Instead of joining DB table with internal table. Try to join two DB tables directly.

SELECT a_{rsnum a}material

b~qty

INTO table itab

FROM DB1 as a INNER JOIN DB2 as b

ON a_{rsnum = b}rsnum .

Hope this will help you,

Regards,

Kusuma.

[Former Member]

Hi,

First u get the data into ur internal table i.e num,material.

SO Now what are the material u have in ur internal table u want to get the qunatity from the DB table

use FOR ALL ENTRIES IN itab WHERE cond

TRY like this..

If ITA is not initial.

Select quan from DBtab into table ITB for all enries in ITA where Matnr = ITA-matnr.

Endif.

[Former Member]

if i want to implement FOR ALL ENTRIES command, is that means both internal table structure must be same? if different can still be implement?

[Former Member]

Hi,

UR internal structure may differ then also no problem..

For all entries wil work,

But ur first internal table must have some data ...

[Former Member]

types : begin of ty_itA

rsnum type rsnum,

material type material,

end of ty_itA.

types : begin of ty_itB

rsnum type rsnum,

material type material,

qty type qty,

end of ty_itB.

types : begin of ty_itc,

rsnum type rsnum,

qty type qty,

end of ty_itC.

data : I_A satandard table of ty_itA,

I_B satandard table of ty_itB,

I_C satandard table of ty_itC.

*NOW FILL THE INTERNAL TABLE I_A.

select rsnum qty from DB_B into table I_C

for all entries in table I_A

WHERE rsnum = I_A-rsnum.

loop at I_C Iinto W_C.

read table I_A with key rsnum = W_C-RSNUM.

IF SY-SUBRC EQ 0.

W_B-RSNUM = W_C-RSNUM.

W_B-QTY = W_C-QTY.

W_B-MATERIAL = W_A-MATERIAL.

ENDIF.

APPEND W_B TO I_B.

CLEAR : W_C,W_A,W_B.

endloop.

[Former Member]

FOR ALL ENTRIES might be work on a single Database table , rite?

since both database and internal table CAN'T put in JOIN condition,

internal table A - ITA : this data is selected between current month and year

rsnum currentdate(month/year)

1111 200808

2222 200808

4444 200808

database table B - DbB -

rsnum matr

1111 01

2222 02

3333 03

4444 04

database table C - DbC

matr previous date(month/year)

01 2008080

02 200801

03 200707

04 200309

• this data show all data from previous and current . Both DbB and DbC need JOIN condition.

expected answer - ITC - show the data if the current date(ITA) = previous date (DbB)

rsnum date

2222 200801

from this example , that's why i'm try to implement the inner join between ITA and DbB and

used internal table as where / join?

[pramod_teewaree]

You can do so as from ABAP 7.5.

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm

[igchavan_1474]

Just use @ symbol before internal table for joins between db table and internal table.