Skip to Content
0
Former Member
Dec 22, 2007 at 07:45 AM

How to link tables EKKO, EKPO, EIKP and EIPO

4245 Views

What I'm trying to do is create a view based on these four tables. EKPO contains multiple rows with the same values for EBELN. EKKO contains all distinct values for EBELN. I am trying to pick up all rows from both tables where EBELN in EKPO is the same as in EKKO. This is simple enough for a select statement to handle, without creating a view. The tricky part is linking the relevant rows selected from EKKO and EKPO to EIKP and EIPO. I tried using EXNUM to link EKKO, EIKP and EIPO, but I'm not sure that EXNUM contains distinct values for all 3 tables. In the case of EIKP it doesn't matter because the values for the fields I want (VORNU and VORNA) will be the same for the rows selected from EKKO and EKPO. The problem is with linking EIPO to my selections. If I knew that EXNUM was distinct for all rows in EIPO there would be no problem. However, I have seen some cases where multiple rows in EIPO have the same values for EXNUM. If I am using EXNUM in EIPO as a key field I have no way of being sure that the fields I want to pick up from EIPO (NACNU, BEHOE, NACD2, PRUEL and NACN3) contain the correct values I require. An example of what I am trying to do might make this clearer:

Table EKKO:

EBELN BEDAT WAERS

420000001 10.08.2007 USD

Table EKPO:

EBELN WERKS MATNR MENGE NETWR NETPR TXZ01

420000001 3500 m1 q1 nw1 cnf1 mat1

420000001 3500 m2 q2 nw2 cnf2 mat2

420000001 3000 m3 q3 nw3 cnf3 mat3

Table EIKP:

VORNU VORDA

125/45 12.08.2007

Table EIPO: (Corresponding to the above three rows in EKPO)

NACNU BEHOE NACD2 PRUEL NACN3

1234 1001 30.08.2007 28.08.2007 xx

1235 1002 27.08.2007 29.08.2007 yy

1236 1003 18.08.2007 16.08.2007 zz

So basically, given the above table entries, I want that the result of my selection should be:

| ROW1 | ROW2 | ROW3

-


EBELN: | 420000001 | 420000001 | 420000001

BEDAT: | 10.08.2007 | 10.08.2007 | 10.08.2007

WAERS: | USD | USD | USD

WERKS: | 3500 | 3500 | 3000

MATNR: | m1 | m2 | m3

MENGE: | q1 | q2 | q3

NETWR: | nw1 | nw2 | nw3

NETPR: | cnf1 | cnf2 | cnf3

TXZ01: | mat1 | mat2 | mat3

VORNU: | 125/45 | 125/45 | 125/45

VORDA: | 12.08.2007 | 12.08.2007 | 12.08.2007

NACNU: | 1234 | 1235 | 1236

BEHOE: | 1001 | 1002 | 1003

NACD2: | 30.08.2007 | 27.08.2007 | 18.08.2007

PRUEL: | 28.08.2007 | 29.08.2007 | 16.08.2007

NACN3: | xx | yy | zz

Sorry for the extremely long post. If anyone can shed some light on this I'd really appreciate it. Thanks.

Kal