topic Re: Join query in Application Development and Automation Discussions

Join query

Former Member — Wed, 06 Jun 2007 12:21:18 GMT

Hi friends,

I need to join AUFK and AUAK based on OBJNR.

Here below is my query.

SELECT A_{AUFNR A}KOKRS A_{OBJNR A}KDAUF

INTO TABLE T_AUFK

FROM AUFK AS A

INNER JOIN

AUAK AS B

ON A_{OBJNR EQ B}OBJNR

WHERE B~PERIO IN S_PERIO

AND B~BUGJAHR IN S_BUGJAH

AND A~WERKS IN S_WERKS.

It is working but not correctly.

For the given condition there is a single record only.

The output is 3 records of same information (duplication of single record).

I found that in aufk only one record where as in auak the objnr is present in 3 records. I think this may be a reason. Pls tell me the correct query to do.

Thank u.

Re: Join query

Former Member — Wed, 06 Jun 2007 12:25:35 GMT

The querry is correct

There may be a 3 settlement Document numbers(BELNR) for the same order(AUFK-OBJNR)

check the field values of BELNR are they same or different.

If they are different it is right.

if they are same they it is wrong.

Then sort the ITAB by AUFNR KOKRS BELNR

and use delete adjacent duplicates from ITAB comparing AUFNR BELNR.

Reward points for useful Answers

Regards

Anji

Re: Join query

Former Member — Wed, 06 Jun 2007 12:27:21 GMT

Hi Murugan,

Try this...

SELECT <i>DISTINCT</i> A_{AUFNR A}KOKRS A_{OBJNR A}KDAUF

INTO TABLE T_AUFK

FROM AUFK AS A

INNER JOIN

AUAK AS B

ON A_{OBJNR EQ B}OBJNR

WHERE B~PERIO IN S_PERIO

AND B~BUGJAHR IN S_BUGJAH

AND A~WERKS IN S_WERKS.

Re: Join query

Former Member — Wed, 06 Jun 2007 12:30:30 GMT

Please try following changed query

SELECT A~AUFNR

B~KOKRS

B~OBJNR

A~KDAUF

INTO TABLE T_AUFK

FROM AUFK AS A

INNER JOIN AUAK AS B

ON A_{OBJNR EQ B}OBJNR

WHERE B~PERIO IN S_PERIO

AND B~BUGJAHR IN S_BUGJAH

AND A~WERKS IN S_WERKS.

Re: Join query

Former Member — Wed, 06 Jun 2007 12:38:22 GMT

Hi,

The problem is bcoz there are 3 records maintained for objnr in auak. Since u r fetching values only from aufk, u will be fetching the same records again and again. I am not sure how exactly the two tables are related. If u have more idea on their relation, then u can add some more condition check so that by the objnr in aufk along with some other field will point to only one record in auak.

Otherwise if its not possible, u can fetch the data into the internal table t_aufk.

Then sort the internal table by objnr.

delete adjacent duplicates from t_aufk.

these will remove the duplicate records.

reward if useful

regards

anoop