on 09-30-2014 2:15 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.