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