sql - i am not able to get the correct SUM(TEMPT.TAXAMT) AS tax -


with tempx  ( select finx.bildat,pr020tbl.resnam,finx.tblnub,menu_descrp,pr026tbl.stwnam,bilnub,taxdescrp,set_descrp,(valamt) kitamt,(taxamt) taxamt,setamt  ( select bstmain.bildat,mainx.rescod,mainx.tblnub,mainx.stwcod,mainx.bilnub, mainx.menu_descrp,mainx.taxdescrp,mainx.valamt,mainx.taxamt,bstmain.set_descrp,bstmain.setamt  ( select kitmain.rescod,kitmain.tblnub,kitmain.stwcod,mentyp,pr604tbl.descrp menu_descrp,  kitmain.taxcod,pr13tbl.lngnam taxdescrp, sum(valamt) valamt,sum(taxamt) taxamt,kitmain.bildat,kitmain.bilnub    (      select main.rescod,main.tblnub,main.stwcod, main.mentyp,sum(main.valamt) valamt,tax.taxcod,sum(tax.taxamt) taxamt,main.bilnub,tax.bildat         (           select rescod,mentyp,kotdat,bilnub,x.tblnub,x.stwcod,sum(valamt) valamt pms.pskittbl x updflg=2 , bilnub<>0 group mentyp,bilnub,kotdat,rescod,tblnub,stwcod       ) main         left outer join        (         select rescod,bilnub,bildat,taxcod,sum(taxamt)taxamt pms.psbtxtbl b  bilnub <>0 group bilnub,bildat,taxcod,rescod       ) tax  on main.bilnub=tax.bilnub , main.kotdat=tax.bildat , main.rescod=tax.rescod group main.mentyp,taxcod,main.bilnub,tax.bildat,main.rescod,main.tblnub,main.stwcod   ) kitmain  -- left outer join ( select reccod,descrp pms.pr604tbl lngcod=1) pr604tbl on kitmain.mentyp = pr604tbl.reccod left outer join ( select taxcod,lngnam pms.pr013tbl) pr13tbl on kitmain.taxcod = pr13tbl.taxcod group mentyp,pr604tbl.descrp,  kitmain.taxcod,pr13tbl.lngnam,kitmain.rescod,kitmain.bildat,kitmain.bilnub,kitmain.tblnub,kitmain.stwcod ) mainx left outer join ( select c.rescod,c.bilnub,c.bildat, (select descrp pms.pr609tbl lngcod=1 , rectyp=16 , reccod in (c.setmod)) set_descrp, sum(setamt) setamt pms.psbsttbl c  bilnub <>0  group setmod,rescod,bilnub,bildat )  bstmain on mainx.rescod=bstmain.rescod , mainx.bilnub=bstmain.bilnub , mainx.bildat=bstmain.bildat   ) finx  left outer join pms.pr020tbl pr020tbl on finx.rescod=pr020tbl.rescod left outer join pms.pr026tbl pr026tbl on finx.stwcod=pr026tbl.stwcod --where bilnub=453   group finx.bildat,menu_descrp,taxdescrp,set_descrp,setamt,bilnub,pr020tbl.resnam,finx.tblnub,valamt,taxamt,pr026tbl.stwnam ) select tempx.bilnub,sum(tempt.taxamt) tax tempx tempt join tempx on tempx.bilnub=tempt.bilnub  tempx.bilnub=453 group tempx.bilnub,tempt.taxamt 

you sum(tempt.taxamt) tax and group by it, self-join table on bilnub not unique. result, taxamt gets multiplied number of matching records. if bilnub unique self-join not make sense:

... select tempx.bilnub, sum(tempt.taxamt) tax --< sum(tempt.taxamt) tempx tempt join tempx on tempx.bilnub = tempt.bilnub     --< self-join on bilnub tempx.bilnub = 453 group tempx.bilnub, tempt.taxamt           --< group tempt.taxamt 

you need format sql , alone reveal quite few issues. can use cte more split query smaller parts debug them individually.


Comments