topic table join in Application Development and Automation Discussions

table join

Former Member — Fri, 10 Nov 2006 10:01:01 GMT

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.

Re: table join

Former Member — Fri, 10 Nov 2006 10:09:26 GMT

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

Re: table join

Former Member — Fri, 10 Nov 2006 10:15:53 GMT

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

Re: table join

Former Member — Fri, 10 Nov 2006 10:40:02 GMT

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

Re: table join

Former Member — Fri, 10 Nov 2006 11:14:27 GMT

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.

Re: table join

Former Member — Fri, 10 Nov 2006 11:50:01 GMT

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^

Re: table join

Former Member — Fri, 10 Nov 2006 11:55:52 GMT

Hi,

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

Regards

Justin