topic Inner join and corresponding fields in Application Development and Automation Discussions

Inner join and corresponding fields

Former Member — Thu, 04 Oct 2007 16:57:55 GMT

Hello people,

I would like to do a inner join between two tables A and B, and put the results in an internal table (using into corresponding fields of).

So, my query is:

select * INTO CORRESPONDING FIELDS OF TABLE ts_eban

FROM eban AS a INNER JOIN ebkn AS b

ON a_{banfn EQ b}banfn

AND a_{bnfpo EQ b}bnfpo

where....

But my ts_eban has 15 fields from eban and only 3 from ebkn.

The field ts_eban-menge is getting the value from ebkn. I would like to take the value from eban. I have to pass all the fields in select clause?

Thanks!

Re: Inner join and corresponding fields

Former Member — Thu, 04 Oct 2007 17:03:12 GMT

Hi,

Yes you will have to pass all the fields in the select statement.

Regards,

Mukesh Kumar

Re: Inner join and corresponding fields

Former Member — Thu, 04 Oct 2007 17:07:55 GMT

You can't use Select * when using Joins between 2 tables

You have to explicitely pass the fields names from those tables see the example code

TYPES: BEGIN OF s_mat,

mblnr TYPE mblnr, " GI Doc No

mjahr TYPE mjahr, " Fiscal Year

zeile TYPE mblpo, " Item No

bwart TYPE bwart, " Movement Type

matnr TYPE matnr, " Material No

werks TYPE werks_d, " Plant

lgort TYPE lgort_d, " St.Location

menge TYPE menge_d, " Quantity

meins TYPE meins, " UOM

hsdat TYPE hsdat, " Production date

vfdat TYPE vfdat, " Shelf Exp.Date

END OF s_mat.

DATA: i_mat TYPE STANDARD TABLE OF s_mat WITH HEADER LINE.

SELECT a~mblnr " GI Doc No

a~mjahr " Fiscal Year

b~zeile " Item No

b~bwart " Movement Type

b~matnr " Material No

b~werks " Plant

b~lgort " St.Location

b~menge " Quantity

b~meins " UoM

b~hsdat " Production date

b~vfdat " Shelf Exp.Date

INTO TABLE i_mat

FROM mkpf AS a JOIN mseg AS b

ON a_{mblnr = b}mblnr JOIN mara AS c

ON b_{matnr = c}matnr

WHERE a~mjahr EQ gv_year AND

b~matnr IN s_matnr AND

b~werks IN s_werks AND

b~lgort IN s_lgort .

Regards

Anji