Get double return for join table

[Former Member]

Dear Experts:

SELECT eban~banfn      "PR number
         eban~bnfpo      "PR item
         eban~bsart      "Doc Type
         eban~bednr      "Requistion tracking number
         eban~ebeln
         eban~ebelp
         eban~zzloc      "GEO code
         ztmm_ccp~zzclli "zzclli code
  INTO CORRESPONDING FIELDS OF TABLE gi_pr_extract
  FROM  ( eban
         INNER JOIN ztmm_ccp
                ON  eban~bednr = ztmm_ccp~bednr
               AND  eban~ebeln = ztmm_ccp~ebeln
               AND  eban~ebelp = ztmm_ccp~ebelp )
  WHERE eban~bsart IN s_bsart.  "11 input doc type

The s_bsart are:

NB

UB

ZNB

ZSC

ZUC

ZVC

ZXC

ZZO

ZZX

ZZY

ZZZ

From above SQL, I got double returen for each eban_{banfn and eban}bnfpo

Row	BANFN	BNFPO	BSART	BEDNR	ZZLOC	ZZCLLI
1 	3100000056	00010	NB			
2 	3100000056	00010	NB			CLLICD DRTP
3 	3100000057	00010	NB			
4 	3100000057	00010	NB			CLLICD DRTP
5 	3100000058	00010	NB			
6 	3100000058	00010	NB			CLLICD DRTP
7 	3100000061	00010	NB			
8 	3100000061	00010	NB			CLLICD DRTP
9 	3100000062	00010	NB			
10 	3100000062	00010	NB			CLLICD DRTP

Do you know why and how do I get the unique one?

Thank you very much!

Helen

Edited by: Thomas Zloch on Oct 4, 2010 3:49 PM - please use code tags

[Former Member]

Hi Helen,

Please check with the below code snippet. Hope it will work.

SELECT eban~banfn      
          eban~bnfpo     
          eban~bsart      
          eban~bednr     
          eban~ebeln
          eban~ebelp
          eban~zzloc      
          ztmm_ccp~zzclli 
   INTO CORRESPONDING FIELDS OF TABLE gi_pr_extract
   FROM  eban
          INNER JOIN ztmm_ccp
                  ON    ztmm_ccp~bednr = eban~bednr
                AND  ztmm_ccp~ebeln = eban~ebeln
                AND  ztmm_ccp~ebelp = eban~ebelp
   WHERE eban~bsart IN s_bsart.

Thanks

Nitesh

[Former Member]

Nitesh,

Thank you for your suggestion.

I tried your code and I got the same result which produce double entry.

It looks like the first one is extracted from EKPO and the second one is extracted from ztmm_ccp table.

How to eliminate the first one record from EKPO? Any other suggestions?

Helen

[Former Member]

You can use the following code just distinct after SELECT

:

 SELECT DISTINCT eban~banfn      
          eban~bnfpo     
          eban~bsart      
          eban~bednr     
          eban~ebeln
          eban~ebelp
          eban~zzloc      
          ztmm_ccp~zzclli 
   INTO CORRESPONDING FIELDS OF TABLE gi_pr_extract
   FROM  eban
          INNER JOIN ztmm_ccp
                  ON    ztmm_ccp~bednr = eban~bednr
                AND  ztmm_ccp~ebeln = eban~ebeln
                AND  ztmm_ccp~ebelp = eban~ebelp
   WHERE eban~bsart IN s_bsart.

With Thanks

Moshiur Rahman

[Former Member]

Thank you for your help.

I tried your DISTINCT SQL and I got the below result which show the same double entry return because of ZZCLLI not same.

Row BANFN BNFPO BSART BEDNR EBELN EBELP ZZLOC ZZCLLI

1 3100000056 10 NB 0

2 3100000056 10 NB 0 CLLICD DRTP

Any other suggestion?

Thank you very much!

Helen

[ThomasZloch]

Obviously there is a 1:N relationship between EBAN and ZTMM_CCP, actually 1:2 in this case, so the inner join is doing what it is supposed to do, duplicating the field values of the left hand table.

What is the primary key of ZTMM_CCP?

You can try adding WHERE-conditions, e.g. ZTMM_CCP-ZZLOC = space, whatever fits your actual requirement.

Thomas

[Former Member]

Thank you for pointing out the 1:N selection.

Helen