topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Thu, 23 Aug 2007 06:23:38 GMT

hi to all,

i have to display data from 3 tables according to select-option, im using inner join for the same.Is my going in right way. Is thr any other method to do the same. I have used inner join but not able to display the data. Please check my code and pt out my mistakes.

Thanks & Regards

Amit

my code :

REPORT zpmr_sample_prog.

TABLES : ekko,

ekpo,

eket.

SELECTION-SCREEN: BEGIN OF BLOCK b1.

SELECT-OPTIONS: s_p_doc FOR ekko-ebeln ,

s_c_code FOR ekko-bukrs ,

s_m_no FOR ekpo-matnr,

s_d_type FOR ekko-bsart.

SELECTION-SCREEN: END OF BLOCK b1.

DATA: BEGIN OF wa,

pur_doc TYPE ekko-ebeln,

pur_item TYPE ekpo-ebelp,

doctype TYPE ekko-bsart,

mat_no TYPE ekpo-matnr,

sch_qty TYPE eket-menge,

plant TYPE ekpo-werks,

co_code TYPE ekko-bukrs,

END OF wa.

DATA: itab LIKE STANDARD TABLE OF wa WITH HEADER LINE.

SELECT a_{ebeln b}ebelp a_{bsart b}matnr c_{menge b}werks INTO CORRESPONDING FIELDS OF TABLE itab

FROM ekko AS a

INNER JOIN ekpo AS b ON a_{ebeln = b}ebeln INNER JOIN eket AS c ON a_{ebeln = c}ebeln WHERE a~ebeln IN s_p_doc AND

a_{bukrs IN s_c_code AND a}bsart IN s_d_type AND b~matnr = s_m_no.

LOOP AT itab.

WRITE : itab-pur_doc, / itab-pur_item, itab-doctype, itab-mat_no, itab-sch_qty, itab-plant.

ENDLOOP.

Re: Inner join

FredericGirod — Thu, 23 Aug 2007 06:27:55 GMT

Stop using INNER JOIN, is always a performance problem after.

use : select .. into table ...

check not it_tab1[] is initial.

select .. into table .. from .. for all entries in it_tab1 ..

check not it_tab2[] is initial.

...

use inner join only between header and item for example : VBAK VBAP, VBRK VBRP ...

Fred

Re: Inner join

Former Member — Thu, 23 Aug 2007 07:00:39 GMT

Hi amit solanki ,

I think all 3 table what u have mention is in relationship, If yes directly u can create the DATABASE VIEW with required fields and use that view in ur program to retrive the data.

For example ur view name is ZDB_VIEW.

select * or fields from ZZDB_VIEW.

if sy-subrc = 0.

Business logic

else.

Business logic

endif.

Re: Inner join

former_member386202 — Thu, 23 Aug 2007 07:03:53 GMT

Hi,

Don't use inner join its performance wise really bad so better to use

for all entries.

first select data from ekko then check if its initial or not then select the data from

ekpo using for all entries and then from eket.

then lastly

loop at it_ekko.

read ekpo and read eket

and append it into on final internal table and then display it.

endloop

regards,

prashant

Re: Inner join

Former Member — Thu, 23 Aug 2007 07:14:23 GMT

hi jawad,

i think using views will be a performance issue. moreover my mentor told me that we dont use views in business logic anymore. watz ur views in this.

thanks

Amit

Re: Inner join

Former Member — Thu, 23 Aug 2007 07:22:51 GMT

Hi,

U can use inner join upto two table. If u use morethan two tables in inner join performance of the program will be degraded.

In any case don't use INTO CORRESPONDING FIELDS OF, because it will effect the performance instead u can define the table according to the selecting fields and using directly INTO TABLE.

REGARDS,

SANKAR

Re: Inner join

Former Member — Thu, 23 Aug 2007 07:34:43 GMT

Hi Amit,

As far as i know there will not be any performance issue here. We can use the DATABASE VIEW to retrive the data from more then one table (If table are related wiht the FK.

Here the one issue is u have to create the VIEW it means it is new object while transporting (To Quality or Production) u have to transport one object extra that is ur VIEW.

Reward If helpfull.

Anees Jawad.

9886358645

Message was edited by:

anees jawad

Re: Inner join

Former Member — Thu, 23 Aug 2007 08:03:30 GMT

Hi,

In your code, so many fields in where conditions used are no-key fields are bukrs,bsart,matnr, etc...........

you can use the following code to avoid joins by using the for all entres concept.

TABLES : ekko,

ekpo,

eket.

DATA: BEGIN OF itab OCCURS 0,

pur_doc TYPE ekko-ebeln,

pur_item TYPE ekpo-ebelp,

doctype TYPE ekko-bsart,

mat_no TYPE ekpo-matnr,

sch_qty TYPE eket-menge,

plant TYPE ekpo-werks,

co_code TYPE ekko-bukrs,

END OF itab.

SELECT-OPTIONS : s_p_doc FOR ekko-ebeln,

s_c_code FOR ekko-bukrs,

s_d_type FOR ekko-bsart,

s_m_no FOR ekpo-matnr.

DATA : BEGIN OF tbl_ekko OCCURS 0,

pur_doc TYPE ekko-ebeln,

co_code TYPE ekko-bukrs,

doctype TYPE ekko-bsart,

END OF tbl_ekko.

DATA : BEGIN OF tbl_ekpo OCCURS 0,

pur_doc TYPE ekko-ebeln,

pur_item TYPE ekpo-ebelp,

mat_no TYPE ekpo-matnr,

plant TYPE ekpo-werks,

END OF tbl_ekpo.

DATA : BEGIN OF tbl_eket OCCURS 0,

pur_doc TYPE ekko-ebeln,

pur_item TYPE ekpo-ebelp,

sch_qty TYPE eket-menge,

END OF tbl_eket.

SELECT ebeln bukrs bsart

INTO TABLE tbl_ekko

FROM ekko

WHERE ebeln IN s_p_doc AND

bukrs IN s_c_code AND

bsart IN s_d_type.

IF sy-subrc = 0.

SORT tbl_ekko BY pur_doc.

ENDIF.

IF NOT tbl_ekko[] IS INITIAL.

SELECT ebeln ebelp matnr werks

INTO TABLE tbl_ekpo

FROM ekpo

FOR ALL ENTRIES IN tbl_ekko

WHERE ebeln EQ tbl_ekko-pur_doc AND

matnr IN s_m_no.

IF sy-subrc = 0.

SORT tbl_ekpo BY pur_doc pur_item.

ENDIF.

IF NOT tbl_ekpo[] IS INITIAL.

SELECT ebeln ebelp menge

INTO TABLE tbl_eket

FROM ekpo

FOR ALL ENTRIES IN tbl_ekpo

WHERE ebeln EQ tbl_ekpo-pur_doc AND

ebelp EQ tbl_ekpo-pur_item.

IF sy-subrc = 0.

SORT tbl_eket BY pur_doc pur_item.

ENDIF.

LOOP AT tbl_ekko.

itab-pur_doc = tbl_ekko-pur_doc.

itab-doctype = tbl_ekko-doctype.

itab-co_code = tbl_ekko-co_code.

CLEAR tbl_ekpo.

READ TABLE tbl_ekpo WITH KEY pur_doc = tbl_ekko-pur_doc

BINARY SEARCH.

IF sy-subrc = 0.

itab-pur_item = tbl_ekpo-pur_item.

itab-mat_no = tbl_ekpo-mat_no .

itab-plant = tbl_ekpo-plant.

CLEAR tbl_eket.

READ TABLE tbl_eket WITH KEY pur_doc = tbl_ekpo-pur_doc

pur_item = tbl_ekpo-pur_item

BINARY SEARCH.

IF sy-subrc = 0.

itab-sch_qty = tbl_eket-sch_qty .

ENDIF.

APPEND itab.

CLEAR : tbl_ekko, itab.

ENDLOOP.

LOOP AT itab.

WRITE : itab-pur_doc, / itab-pur_item, itab-doctype, itab-mat_no, itab-sch_qty, itab-plant.

ENDLOOP.

*******************************************

Let me know if you have doubts.

Points if useful.

Regards,

Vijay.

Re: Inner join

valentin_catalin2 — Thu, 23 Aug 2007 08:20:37 GMT

Hi,

Your join has two problems:

1. in your definition of internal table where the data should be saved you have changed the name of the fields (instead of EBELN -> PUR_DOC); thats why you should change also the name of the fields in the select statement. This was the main reason not to get any data

2. in your join definition you should join EKPO with EKET by using EBELN and EBELP fields (this is the primary key for EKET table ...)

Your join should look like this:

SELECT 
a~ebeln as pur_doc
b~ebelp as pur_item
a~bsart as doctype
b~matnr as mat_no
c~menge as sch_qty
b~werks as plant
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ekko AS a INNER JOIN ekpo AS b ON a~ebeln = b~ebeln 
	       INNER JOIN eket AS c ON b~ebeln = c~ebeln and
                                       b~ebelp = c~ebelp
WHERE a~ebeln IN s_p_doc AND
      a~bukrs IN s_c_code AND 
      a~bsart IN s_d_type AND 
      b~matnr = s_m_no.

Related to the other comments and answers posted to your question:

- joining table is not a bad performance solution at all. I have programs with joins between 10 or 15 tables (ALL by PRIMARY KEY !!!) and they are performing really fast. This is because the select is done by the database from the back directly and this database is optimized for this kind of tasks.

- if you are using FOR ALL ENTRIES ... you should take a lot of care to that the internal table content referred should not have to many lines, other wise your performance can go down.

BR,

Valentin