table join

[Former Member]

Hi all,

I have a requirement in which I have 6 inputs project no, wbs no, cost center no, plant no, purchasing group, po creation date. I need to bring the po details as the report. I have coded a select query joining all the 4 tables involved in the input as below.Is it correct ? will I get the desired output for any combinations of input if i join? because all the inputs are optional.

SELECT a~ebeln

a~bsart

a~lifnr

a~ekgrp

a~bedat

a~waers

b~werks

c~ps_psp_pnr

c~kostl

d~psphi

INTO CORRESPONDING FIELDS OF TABLE t_po_hdr

FROM ekko as a

JOIN ekpo as b ON a_{ebeln = b}ebeln

JOIN ekkn as c ON a_{ebeln = c}ebeln

JOIN prps as d ON c_{ps_psp_pnr = d}pspnr

WHERE a~bsart IN ('PO','EBP')

AND a~ekgrp IN s_pg_no

AND a~bedat IN s_po_dt

AND b~werks IN s_pl_no

AND c~ps_psp_pnr IN s_wbs_no

AND c~kostl IN s_cc_no

AND d~psphi IN s_proj.

[Former Member]

I think you would be able to get the required data but it would be advisable to separate it into two SELECTs as I feel it might lead to performance issues.

U can look at joining ekko, ekpo and ekkn in one select and using for all entries get the data from prps.

Regards

Anurag

[Former Member]

U will get the ouput. But it is not suggested to have a join on four tables.

Better u seperate the select statement in two and then take the data into one internal table.

SELECT a~ebeln

a~bsart

a~lifnr

a~ekgrp

a~bedat

a~waers

b~werks

INTO CORRESPONDING FIELDS OF TABLE t_po_hdr

FROM ekko as a

JOIN ekpo as b ON a_{ebeln = b}ebeln

WHERE a~bsart IN ('PO','EBP')

AND a~ekgrp IN s_pg_no

AND a~bedat IN s_po_dt

AND b~werks IN s_pl_no.

SELECT c~ps_psp_pnr

c~kostl

d~psphi

INTO CORRESPONDING FIELDS OF TABLE t_po_hdr1

FROM ekkn as c

JOIN prps as d ON c_{ps_psp_pnr = d}pspnr

WHERE c~ps_psp_pnr IN s_wbs_no

AND c~kostl IN s_cc_no

AND d~psphi IN s_proj.

Best Regards,

Vibha

*Please mark all the helpful answers

[Former Member]

hi,

I suggest u to use SELECT with FOR ALL ENTRIES option instead of JOIN if it is not mandatory for performance wise to be gud

select ebeln bsart lifnr ekgrp bedat waers

from ekko

into table itab1 where bsart in ('PO','EBP')

and ekgrp IN s_pg_no

and bedat IN s_po_dt

select ebeln werks

from ekpo

into table itab2

for all entries in itab1

where ebeln = itab1-ebeln

and werks IN s_pl_no.

select ebeln ps_psp_pnr kostl

from ekkn

into table itab3

for all entries in itab1

where ebeln = itab1-ebeln

and ps_psp_pnr IN s_wbs_no

and kostl IN s_cc_no.

select ps_psp_pnr psphi

from prps

into table itab4

for all entries in itab3

where ps_psp_pnr = itab3-ps_psp_pnr

and psphi IN s_proj.

check if u have missed any key fields in where clause

do assign points if it helps you.

Message was edited by:

sowjanya suggula

[Former Member]

Hi,

For better performance pl ignore to join more than two tables . U can select all necessary data by joining EKPO & EKKO tables into one internal table say itab1. Then If the content of itab1 is not initial then select rest of the data using FOR ALL ENTRIES IN ITAB1 .

If sounds good pl reward,

Cheers.

[Former Member]

hi

good

go through this join statement which ll give you how to join more than 3 tables

SELECT c~matnr "Material no.

• c~werks "Plant

• c~lvorm "Flag Mat.-Del. at Plant level

• c~bwtty "Valuation category

• c~kzkri "Indicator: Gritical part

• c~dispr "Material: MRP profile

• ADD Mike Krepcik TD 3363 02/01/05 D10K913640

• c~dismm "MRP type

• END TD 3363 02/01/05 D10K913640

• c~plifz "Planned delivery time in days

• c~perkz "Period indicator

• c~minbe "Reorder point

• c~mabst "Maximum stock level

• c~umlmc "Stock in Tran. (plant to plant)

• a~mtart "Material type

• a~meins "Base unit of measure

• k~spras "Language key

• k~maktx "Material description

• w~bwkey "Valuation area

• w~bwtar "Valuation type

• w~lbkum "Total value stock

• w~salk3 "Value of total valuated stock

• w~verpr "Moving ave. price/periodic unit

• w~peinh "Price unit ADD D10K913194

• FROM marc AS c LEFT OUTER JOIN mara AS a

• ON c_{matnr = a}matnr

• LEFT OUTER JOIN makt AS k

• ON c_{matnr = k}matnr

• LEFT OUTER JOIN mbew AS w

• ON c_{matnr = w}matnr AND

• c_{werks = w}bwkey

• INTO TABLE i_splus

• WHERE c~matnr IN s_matnr "Material no.

• AND c~werks IN s_werks. "Plant

thanks

mrutyun^

[Former Member]

Hi,

U can split into 2 select statments and use for all enteris.

Regards

Justin