July 16, 2016 at 10:25 pm
-------Querry
SELECT 5.0 AS NoOfCopy, BaseTbl.*, PartyMst.Party_Name, PartyMst.Party_Add1,
PartyMst.Party_Add2, PartyMst.Party_Add3,PartyMst.Party_City,
PartyMst.Party_Pin, PartyMst.Party_Cont, PartyMst.Party_Tel, PartyMst.Mobile_No, PartyMst.Party_CST, PartyMst.Party_LST,
PartyMst.CST_Date, PartyMst.LST_Date, PartyMst.Party_Coun, PartyMst.Party_CST as PParty_CST,
PartyMst.CST_Date as PParty_CST_Date, PartyMst.Party_LST as PParty_LST, PartyMst.LST_Date as PParty_LST_Date,
PartyMst.ExLic_No as PParty_ExLic_No, PartyMst.Ex_Range as PParty_Ex_Range, PartyMst.Ex_Div as PParty_Ex_Div,
PartyMst.Ex_ColCt as PParty_Ex_ColCt, document.Doc_Descrp ,SPACE(1) as DC_Desc, Ctrlact.Comp_Add1, Ctrlact.Comp_Add2,
Ctrlact.Comp_City, Ctrlact.Comp_Pin, Ctrlact.Comp_Count, Ctrlact1.Comp_Std, Ctrlact.Comp_Telf, Ctrlact1.Comp_Fax,
SPACE(1) as PinNo, Ctrlact1.Comp_Email as EMail_ID, Ctrlact1.Cst_TinNo, Ctrlact1.Cst_TinDt, Ctrlact1.Vat_TinNo,
Ctrlact1.Vat_TinDt ,Ctrlact.Ex_Colct, Ctrlact.Ex_Div, Ctrlact.Ex_Range, Ctrlact.ExLic_No, Ctrlact.Ex_State,
Ctrlact1.EdCes_Per, Ctrlact1.HedCs_Per, Ctrlact.OlpPath AS Logo_Path, Ctrlact.Comp_Add1 as Reg_Add1,
Ctrlact.Comp_Add2 as Reg_Add2, Ctrlact.Comp_City as Reg_City, Ctrlact.Comp_Pin as Reg_Pin,
Ctrlact.Comp_Count as Reg_Count, Ctrlact1.Comp_Std as Reg_Std, Ctrlact.Comp_Telf as Reg_Telf,
Ctrlact1.Comp_Fax as Reg_Fax, Ctrlact1.Comp_Email as Reg_EMail,
PartyLoc.Party_No as Cnsg_No,
PartyLoc.Cnsg_Name, PartyLoc.Cont_Per, PartyLoc.Party_SAd1, PartyLoc.Party_SAd2,
PartyLoc.Party_SAd3, PartyLoc.Party_SCit,
PartyLoc.Party_SPin, PartyLoc.Party_CST as CParty_CST, PartyLoc.CST_Date as CParty_CST_Date,
PartyLoc.Party_LST as CParty_LST, PartyLoc.LST_Date as CParty_LST_Date, PartyLoc.ExLic_No as CParty_ExLic_No,
PartyLoc.Ex_Range as CParty_Ex_Range, PartyLoc.Ex_Div as CParty_Ex_Div, PartyLoc.Ex_ColCt as CParty_Ex_ColCt,
PartyItm.P_Im_Code, PartyItm.P_Im_Desc, Export.Coun_Orgn, Export.Pre_Carge, Export.VesFlg_No, Export.Port_Load,
Export.Port_Disc, Export.Coun_Dstn, CurMst.Cur_Desc, CurMst.Cur_Denm, CurMst.Cur_Symb ,SrvTxMst.Srv_Desc,
SrvTxMst.SrvRg_No ,ExPlant.Ex_ColCt as Ex_ColCt_P, ExPlant.Ex_Div as Ex_Div_P, ExPlant.Ex_Range as Ex_Range_P,
ExPlant.ExLic_No as ExLic_No_P, ExPlant.Ex_State as Ex_State_P ,ExPlant.Loc_Name,
(convert(varchar(250),MsgMst.NOTES)) as NOTES,f9brkpdt.F9_PRRT1,
Shipping.PARTY_NAME as Shipping_Name,Shipping.PARTY_ADD1 as Shipping_Add1,
Shipping.PARTY_ADD2 as Shipping_Add2,TrinvsB.PARTY_ADD3 As Shipping_Add3,
Shipping.PARTY_CITY AS Shipping_City,Shipping.PARTY_PIN as Shipping_Pin ,
CPINVDBF.BASIC_PER,trvchr.AM_CODE,
TaxRef.Tax_Desc,(TaxRef.TAX9_PER*100) as Vat_Per,
reftaxv.Tax_Desc As HTaxDesc,(reftaxv.TAX9_PER*100) as NwVatPer
FROM ( SELECT Trinvs.Doc_Type, Trinvs.Doc_No, Trinvs.Doc_Date,Trinvs.PDoc_Type, Trinvs.PDoc_No, Trinvs.RDoc_Type, Trinvs.RDoc_No,
Trinvs.Dc, Trinvs.Party_St, Trinvs.Party_No, Trinvs.Form_Type, Trinvs.Doc_Ref, Trinvs.Your_Ref,
Trinvs.Your_Date, Trinvs.Due_Date, Trinvs.Post_Flag, Trinvs.User_ID, Trinvs.Date_Stmp, Trinvs.Time_Stmp,
Trinvs.Party_BLoc, Trinvs.Lr_No, Trinvs.Lr_Date, Trinvs.Doc_Value, Trinvs.Doc_Tax0 as Tax0,
Trinvs.Doc_Tax1 as Tax1, Trinvs.Doc_Tax2 as Tax2, Trinvs.Doc_Tax3 as Tax3, Trinvs.Doc_Tax4 as Tax4,
Trinvs.Doc_Tax5 as Tax5, Trinvs.Doc_Tax6 as Tax6, Trinvs.Doc_Tax7 as Tax7, Trinvs.Doc_Tax8 as Tax8,
Trinvs.Doc_Tax9 as Tax9, Trinvs.Doc_Tax10 as Tax10 ,Trinvs.Truck_No ,Trinvs.IGin_Type, Trinvs.IGin_No ,TrinvsA.Trpt_Per,
TrinvsA.Cess_Amt ,Trinvs.Srv_Code, Trinvs.Srv_Per ,TrinvsA.SRV_Edcs, TrinvsA.HghSrvEcs ,Trinvs.RndOf_Comp ,
Trinvs.Doc_Tax11 as Tax11, Trinvs.Doc_Tax12 as Tax12, Trinvs.Doc_Tax13 as Tax13 ,Trinvs.Doc_Tax20 AS TAX20,
Trinvs.Doc_Tax21 AS TAX21, Trinvs.Doc_Tax22 AS TAX22 ,Trinvs.AUser_ID ,Trinvs.Cur_Code, Trinvs.Doc_FCAmt,
Trinvs.ImPo_Type ,Trinvs.Ded_Cd, Trinvs.Ded_Amt,' ' as Veh_No ,Trinvs.Trnp_St, Trinvs.Trnp_No ,Trinvs.Eff_Date ,
Trinvs.DOC_TAX2 as Excise_P ,Trinvs.DOC_TAX3 as Excise_S ,Trinvs.DOC_TAX4 as Vat_P ,Trinvs.DOC_TAX4 as Vat_S ,
Trinvs.DOC_TAX6 as SrvTax_P ,Trinvs.DOC_TAX6 as SrvTax_S ,Trinvs.DOC_TXCD As DocTxcd,
Imtrans.Loc_Type, Imtrans.Im_Loc, Imtrans.Im_Code,Imtrans.Im_Qty, Imtrans.Im_ReqQty, Imtrans.Im_QtyRxd, Imtrans.Im_ChlQty,Imtrans.Im_RejQty, Imtrans.Rej_Cd,
Imtrans.Im_InvRt, Imtrans.Im_FcRate, Imtrans.Im_SaleRt, Imtrans.Im_NewRt,Imtrans.Im_Basic, Imtrans.Im_FCAmt,
Imtrans.Doc_Tax0, Imtrans.Doc_Tax1, Imtrans.Doc_Tax2, Imtrans.Doc_Tax3,Imtrans.Doc_Tax4, Imtrans.Asses_Val,
Imtrans.Im_CsAmt, Imtrans.Im_Value1, Imtrans.Doc_Txcd,Imtrans.Frt_Amt, Imtrans.Imp_Amt, Imtrans.Msc_Amt,
Imtrans.Ins_Amt, Imtrans.Oct_Amt, Imtrans.Agent_Chg,Imtrans.IM_VALUE2,Imtrans.Lc_Chg, Imtrans.Ex_Per, Imtrans.Ex_Code,
Imtrans.RG1_EntNo, Imtrans.RG23_EntNo, Imtrans.GP_No, Imtrans.EXCS_RATE ,Imtrans.ID_Key ,Imtrans.Im_Lot ,Imtrans.Projct_No ,
Imtrans.BSo_No ,SPACE(1) as Co_Cd ,SPACE(1) as Pharma_Cp ,Imtrans.Im_Descr as Line_Desc, Imtrans.NItm_UOM ,
Imtrans.Tex_duty, Imtrans.Tex_EdCs, ImtransA.Tex_HedCs ,Imtrans.Doc_Tax20, Imtrans.Doc_Tax21, Imtrans.Doc_Tax22 ,
' ' as Set_Type, 0.00 as SetOf_Amt ,Imtrans.Im_AuxRt, Imtrans.Im_AuxQty ,ImtransA.HghEdcs ,MaitemH.Im_Descr,
MaitemH.Im_GDesc, MaitemH.Im_Uom, MaitemH.Im_DrgNo, MaitemH.Im_Status,trvchr.AM_CODE,trvchr.YOUR_REFNO,trvchr.ACC_AMT
--(SELECT DISTINCT (REPLACE(ACC_AMT,'-','')) FROM trvchr where AM_CODE = 'I1010107') as ass_amt,
-- (SELECT DISTINCT (REPLACE(ACC_AMT,'-','')) FROM trvchr where AM_CODE = 'L4320104')as srvbaS_amt,
-- (SELECT DISTINCT (REPLACE(ACC_AMT,'-','')) FROM trvchr where AM_CODE = 'L4320119')as Sbc_amt,
-- (SELECT DISTINCT (REPLACE(ACC_AMT,'-','')) FROM trvchr where AM_CODE = 'L4320121')as KKC_Amt,
-- (SELECT DISTINCT (REPLACE(ACC_AMT,'-','')) FROM trvchr where AM_CODE = 'L4330101')as vat_amt
FROM Trinvs, TrinvsA, Imtrans, ImtransA,trvchr, MaitemH
WHERE Trinvs.Doc_Type = ' 27' And Trinvs.Doc_No Between 'WCT011617000006' And 'WCT011617000006'
--WHERE Trinvs.Doc_Type = ?DocType And Trinvs.Doc_No Between ?FDocNo And ?TDocNo
And Trinvs.Doc_Type = TrinvsA.Doc_Type And Trinvs.Doc_No = TrinvsA.Doc_No
And trvchr.Doc_Type = TrinvsA.Doc_Type And trvchr.Doc_No = TrinvsA.Doc_No
And Trinvs.Doc_Type = Imtrans.Doc_Type And Trinvs.Doc_No = Imtrans.Doc_No
And Imtrans.Doc_Type = ImtransA.Doc_Type And Imtrans.Doc_No = ImtransA.Doc_No
And Imtrans.RDoc_Type = ImtransA.RDoc_Type And Imtrans.RDoc_No = ImtransA.RDoc_No
And Imtrans.Loc_Type = ImtransA.Loc_Type And Imtrans.Im_Loc = ImtransA.Im_Loc
And Imtrans.Im_Code = ImtransA.Im_Code And Imtrans.Im_Lot = ImtransA.Im_Lot
And Imtrans.Projct_No = ImtransA.Projct_No And Imtrans.BSo_No = ImtransA.BSo_No
And Imtrans.Im_Descr = ImtransA.Im_Descr And Imtrans.Im_Code = MaitemH.Im_Code ) BaseTbl
Left Outer Join trvchr ON (BaseTbl.Doc_no = trvchr.Doc_no and BaseTbl.AM_CODE=trvchr.AM_CODE
and BaseTbl.YOUR_REFNO=trvchr.YOUR_REFNO)
Left Outer Join TaxRef ON (BaseTbl.Doc_Txcd = TaxRef.Tax_Cd)
left outer join taxref reftaxv On (BaseTbl.DocTxcd = reftaxv.TAX_CD)
Left Outer Join PartyLoc ON (BaseTbl.Party_St = PartyLoc.Party_St And BaseTbl.Party_No = PartyLoc.Party_No
And BaseTbl.Party_BLoc = PartyLoc.Party_BLoc)
Left Outer Join PartyItm ON (BaseTbl.Party_St = PartyItm.Party_St And BaseTbl.Party_No = PartyItm.Party_No
And BaseTbl.Im_Code = PartyItm.F_Im_Code And BaseTbl.Party_BLoc = PartyItm.Party_BLoc )
Left Outer Join Export ON (BaseTbl.Doc_No = Export.RDoc_No2)
Left Outer Join CurMst ON (BaseTbl.Cur_Code = CurMst.Cur_Code)
Left Outer Join SrvTxMst ON (BaseTbl.Srv_Code = SrvTxMst.Srv_Code)
Left outer Join trinvs Shipping ON(Shipping.DOC_TYPE=BaseTbl.PDOC_TYPE And Shipping.DOC_No=BaseTbl.PDOC_NO )
Left Outer Join trinvsb On(trinvsb.DOC_TYPE=BaseTbl.PDOC_TYPE And trinvsb.DOC_No=BaseTbl.PDOC_NO )
Left Outer Join f9brkpdt On (f9brkpdt.DOC_TYPE=BaseTbl.Doc_typeand f9brkpdt.DOC_NO =BaseTbl.DOC_NO and f9brkpdt.PROJCT_NO =BaseTbl.Projct_No )
Left Outer Join MsgMst ON (BaseTbl.Doc_Type = MsgMst.Doc_Type And BaseTbl.Doc_No = MsgMst.Doc_No And BaseTbl.Im_Code = MsgMst.S_Im_Code And BaseTbl.Im_Lot = MsgMst.Im_Lot And BaseTbl.Projct_No = MsgMst.Projct_No)
,PartyMst, Document, Ctrlact, Ctrlact1,CPINVDBF,LocMst
Left Outer Join Explant ON (LocMst.Im_ExLoc = Explant.Im_ExLoc)
WHERE BaseTbl.Party_St = PartyMst.Party_St And BaseTbl.Party_No = PartyMst.Party_No
And SUBSTRING(BaseTbl.Doc_Type,4,2) = document.Doc_LType
And SUBSTRING(BaseTbl.Doc_No,1, 5) = document.Sr
And BaseTbl.Loc_Type = LocMst.Loc_Type And BaseTbl.Im_Loc = LocMst.Im_Loc
AND BaseTbl.DOC_TYPE = CPINVDBF.DOC_TYPE AND BaseTbl.DOC_NO = CPINVDBF.DOC_NO
ORDER BY BaseTbl.Doc_Type, BaseTbl.Doc_No, BaseTbl.ID_Key
July 16, 2016 at 10:47 pm
Quick thoughts, change the old style ANSI joins to normal joins, move any join conditions from the where clause to the joins and then check for any remaining LEFT table conditions which might force an inner join in the execution plan.
😎
This query is rather unclear and obfuscated, suggest you format it, use the proper code tags when posting and provide a runnable example schema and sample data.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply