topic duplicate value in Inner join in Application Development and Automation Discussions

duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 09:10:31 GMT

hi all,

i m using inner join for 3 tables but problem is that i m getting repeated values while table contains only

2 record but inner joi shows me 4.

codes : SELECT m1_{pernr m2}lgart m2_{endda m2}betrg m2_{zuord m3}lga01 m3_{bet01 m3}begda INTO TABLE itab

FROM pa0000 AS m1 INNER JOIN pa0015 AS m2 ON m1_{pernr = m2}pernr

INNER JOIN pa0589 AS m3 ON m1_{pernr = m3}pernr

WHERE m1_{pernr IN pernr AND m1}endda = '99991231' AND m1~stat2 = '3'

AND m2~lgart = '4004'

AND m3~lga01 = '4004'.

getting values in itab like :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 5004.00

8430 4004 5834.00 1000.00

8430 4004 8748.00 1000.00

while it must be like this :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 1000.00

can anyone please help me.

regards saurabh.

Re: duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 09:17:00 GMT

Hi,

Use FOR ALL ENTRIES Statement.

Duplicates will get deleted automatically.

Vikas.

Re: duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 09:18:18 GMT

Else.

use DELETE ADJACENT DUPLICATES statement.

Vikas.

Re: duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 09:37:24 GMT

hi all,

thanx for reply, actually this is my first query so i can use for all entries, there are only one record in table PA0000 and 2 in PA0015 and 2 is also in PA0589. so i need only two records in itab. but this join is giving 4 records. can anyone please help me?

Re: duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 09:59:27 GMT

You will have to match all the primary keys of the three tables.

regards,

Jinson.

Re: duplicate value in Inner join

Former Member — Sat, 10 Jan 2009 10:28:10 GMT

Hello Saurabh,

Include all the primary key fields in the where clause for PA0015 and PA0589, while comparing with those in PA0000. Since the key fields for all these '3' tables are same this should not be a problem.

If you have any reservations about changing the select query, then, sort your final internal table with a defined sort key (based on your requirement) and use "Delete adjacent duplicates" with the same sort key.

Please let me know if this helps.

Regards,

Pavan