cancel
Showing results for 
Search instead for 
Did you mean: 

Getting duplicate records in full outer join...

Former Member
0 Kudos

Dear Folks,

I am getting duplicate records during full outer join.

it_out = select c1.MANDT, c1.BUKRS, c1.WAERS, c1.KTOPL, c1.GSBER, c1.VRGNG,

   c1.PERNR, c1.EBELP, c1.MEINB, c1.KSTAR, c1.MATNR, c1.PBUKRS, c1.FKBER,

   c1.KOKRS, c1.BELNR, c1.BUZEI, c1.SGTXT, c1.VERSN, c1.MEINH, c1.PFKBER,

   c1.REFBN, c1.BUDAT, c1.CPUTM, c1.CPUDT, c1.BLDAT, c1.KOSTL, C1.SMEV,

   c1.SMEF, c1.SMEG, c1.SMEG1, c1.SWF, c1.SWG, c1.LSTAR, c1.PRCTR, c1.fiscper as FISCPER_ACT,

   C2.fiscvar, c2.fiscper, c2.kokrs as kokrs_p, c2.kostl as kostl_p,

   c2.vtype, c2.versn as versn_p, c2.kstar as kstar_p, c2.seknz, c2.valutyp,

   c2.meastype, c2.swg as swg_p, c2.swf as swf_p, c2.swv as swv_p, c2.waers as waers_p,

       c2.vrgng as vrgng_p, c2.vtdetail, c2.vtstat, c2.lstar as lstar_p

        

      

       from "_SYS_BIC"."dev/CV_CCA" as C1

       full outer join "_SYS_BIC"."dev/CV_CCL_PLAN" as C2

        on c1.mandt = c2.mandt and

          c1.KOSTL = c2.KOSTL and

          c1.KOKRS = c2.KOKRS and

          c1.kstar = c2.kstar and

          c1.FISCPER = c2.fiscper ;

Is there any way to solve this problem.

-

Regards,

Amol

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Amol,

As suggested by Ajay, you can easily get rid of this issue by simple adding DISTINCT keyword after SELECT keyword. e.g. SELECT DISTINCT column1, column2,... column N FROM table_name

You can use any type of join in query.

0 Kudos

You may add DISTINCT in the SELECT statement. This will remove the duplicates.

select DISTINCT c1.MANDT, c1.BUKRS, c1.WAERS, c1.KTOPL, c1.GSBER, c1.VRGNG,

   c1.PERNR, c1.EBELP, c1.MEINB, c1.KSTAR, c1.MATNR, c1.PBUKRS, c1.FKBER,

   c1.KOKRS, c1.BELNR, c1.BUZEI, c1.SGTXT, c1.VERSN, c1.MEINH, c1.PFKBER,

   c1.REFBN, c1.BUDAT, c1.CPUTM, c1.CPUDT, c1.BLDAT, c1.KOSTL, C1.SMEV,

   c1.SMEF, c1.SMEG, c1.SMEG1, c1.SWF, c1.SWG, c1.LSTAR, c1.PRCTR, c1.fiscper as FISCPER_ACT,

   C2.fiscvar, c2.fiscper, c2.kokrs as kokrs_p, c2.kostl as kostl_p,

   c2.vtype, c2.versn as versn_p, c2.kstar as kstar_p, c2.seknz, c2.valutyp,

   c2.meastype, c2.swg as swg_p, c2.swf as swf_p, c2.swv as swv_p, c2.waers as waers_p,

       c2.vrgng as vrgng_p, c2.vtdetail, c2.vtstat, c2.lstar as lstar_p

       

     

       from "_SYS_BIC"."dev/CV_CCA" as C1

       full outer join "_SYS_BIC"."dev/CV_CCL_PLAN" as C2

        on c1.mandt = c2.mandt and

          c1.KOSTL = c2.KOSTL and

          c1.KOKRS = c2.KOKRS and

          c1.kstar = c2.kstar and

          c1.FISCPER = c2.fiscper ;

Also please check what the business requirement is for the FULL OUTER JOIN. By definition, this join will produce all combinations and generally has the potential to create lot of duplicate records.

Former Member
0 Kudos

This message was moderated.