Problem with View.

[Former Member]

Hi

I am trying to create a view with the following tables: COEP,COBK,PA0001 AND PRPS. So, the main intention is I want employee name from PA0001 based on Personnel Number from COEP and POSID, i.e the WBS element from PRPS based on object number from COEP.

Now, coep and cobk(mandt,kokrs and belnr) have three fields which need to be put in join conditions and all the three are key fields in the respective tables. So, no issue till that.

So, in the next join condition I gave pa0001-pernr = coep-pernr and pa0001-mandt = coep-mandt respecting the client dependancy of the tables.

Also, in the same way for PRPS, i gave mandt and objnr in the join conditions with the other table being coep.

Now, in coep objnr and pernr are not key fields..and objnr is not a key field in PRPS also. Only pernr is a key field in pa0001.

The issue here is: Each record is shown twice. There is a duplicate record for every record. I think the issue is with the key fields.

Any ideas please??

Regards,

Vishwa.

[Former Member]

Check your table/join condition carefully.

Did you maintain your key fields relation with any other table in table/join condition?

i guess this consequences happen when we not maintain our key fields relation in table/join condition tab .

You must add key fields in table/join condition with other table like you are joinning non key fields,because View always give prefer your join condition over key fields,cause basic defination of view is depend on join only .Check once.

Edited by: Amit Gujargoud on Nov 17, 2008 6:35 AM

[Former Member]

Amit, for all th four tables I used here are the join conditions:

COBK-MANDT = COEP-MANDT, 
COBK-KOKRS = COEP-KOKRS,
COBK-BLENR = COEP-BELNR,   "these three are key fields for their respective tables
PA0001-MANDT = COEP-MANDT,"this is also key field join
PA0001-PERNR = COEP-PERNR, "here pernr is key field only with pa0001, but not with COEP.
PRPS-MANDT = COEP-MANDT,
PRPS-OBJNR = COEP-OBJNR," not a key field in either of the tables.

Now, after seeing your answer, I am realizing that I should not have put join condition for non key fields..Is that true?? Just mandt field will do for the join?

PS: other than mandt and objnr,no other common fields between PRPS and COEP..and in PA0001 also only PERNR and MANDT are the common fields.

Regards,

Vishwa.

Edited by: Vishwa Sri Hari on Nov 17, 2008 11:15 AM

[Former Member]

Guys, waited all day for answers!! Anyways, I removed pa0001 table from the view and now its working perfectly.

So, another select statement and another loop wouldbe required just to pick up a field from pa0001.

However,I am still open to suggestions. :).

Thanks and Regards,

Vishwa.