topic need help in Join statements in Application Development and Automation Discussions

need help in Join statements

Former Member — Sat, 09 Feb 2008 07:36:01 GMT

Need to join these tables EKPO(Purchasing Doc Item), EKET(Scheduling Agreement Schedule line), MAKT(Material Desc), LIPS(SD Doc: Delivery Item data) tables.

The fields that EKPO has is ebeln,ebelp,matnr,menge, meins

The fields that EKET has is ebeln,ebelp,etenr,dat01.

The fields that MAKT has is matnr,spras,makts.

The fields that LIPS has is vbeln,posnr,matnr,vgpos,erdat,vrkme,lfimg.

The output needs to as

ebeln from table ekpo-ebeln

ebelp from table lips-vgpos

matnr from table ekpo-matnr

menge from table ekpo-menge

meins from table ekpo-meins

vrkme from table lips-vrkme

vbeln from table lips-vbeln

lfimg from table lips-lfimg

erdat from table lips-erdat

dat01 from table eket-dat01

maktx from table makt-maktx

The selection screen has ekpo-ebeln and eket-dat01 as select-options

and parameters as ekpo-bukrs and ekpo-werks.

Please suggest as to how to write this join with the above tables.

Re: need help in Join statements

Former Member — Sat, 09 Feb 2008 08:02:11 GMT

hai see the fields in tables vbfa and vbpa. i think we hav to use one of these to join ur tables.

Edited by: sudheer b on Feb 9, 2008 9:02 AM

Re: need help in Join statements

Former Member — Sat, 09 Feb 2008 08:42:20 GMT

Hi,

I tried joining your tables using inner join. Here is sample code which I have written.

REPORT ZAM_TABLEJOIN.

tables: ekpo,eket,makt,lips.

select-options: s_ebeln for ekpo-ebeln,

s_dat01 for eket-dat01.

parameters: p_bukrs type ekpo-bukrs,

p_werks type ekpo-werks.

types: begin of itabtype,

ebeln type ekpo-ebeln,

ebelp type lips-vgpos,

matnr type ekpo-matnr,

menge type ekpo-menge,

meins type ekpo-meins,

vrkme type lips-vrkme,

vbeln type lips-vbeln,

lfimg type lips-lfimg,

erdat type lips-erdat,

dat01 type eket-dat01,

maktx type makt-maktx,

end of itabtype.

data: itab type standard table of itabtype,

wa like line of itab.

SELECT a~ebeln

d~vgpos

a~matnr

a~menge

a~meins

d~vrkme

d~vbeln

d~lfimg

d~erdat

b~dat01

c~maktx

FROM ekpo AS a

INNER JOIN eket AS b

ON a_{ebeln EQ b}ebeln AND a_{ebelp EQ b}ebelp

INNER JOIN makt AS c

ON a_{matnr EQ c}matnr

INNER JOIN lips AS d

ON a_{matnr EQ d}matnr

INTO TABLE itab

WHERE a_{ebeln IN s_ebeln AND b}dat01 IN s_dat01 AND a~bukrs EQ p_bukrs

AND a~werks EQ p_werks.

loop at itab into wa.

write: / wa-ebeln, wa-ebelp,wa-matnr,wa-menge, wa-meins, wa-vrkme,

wa-vbeln, wa-lfimg, wa-erdat, wa-dat01, wa-maktx.

endloop.

Hope this helps you. Please correct me if there is anything wrong in this code.

Reward All Helpful Answers!!!!

Sunny