(T-SQL)-Where to place JOIN in a Stored Procedure plz

[markajem]

Where in the procedure below would I place this join? I want view_lot_invoice_history.docnumbr to dictate which records are pulled from sop30200.sopnumbe; whereas the only results from sop30200 would be that equal view_lot_invoice_history.docnumbr

INNER JOIN ACME.dbo.view_lot_invoice_history
ON SOP30200.SOPNUMBE = view_lot_invoice_history.DOCNUMBR

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER            procedure ALTRSOPInvoiceHistory
 @DOCTYPE smallint,
 @CMPNTSQ smallint,
 @FORCFN smallint
as
SELECT
    	SOP30200.SOPNUMBE, SOP30200.ORIGTYPE, SOP30200.SOPTYPE,SOP30200.SOPTYPE DOCTYPE,
	SOP30200.ORIGNUMB, SOP30200.DOCID, SOP30200.DOCDATE, SOP30200.PYMTRMID, 
	SOP30200.CUSTNMBR, SOP30200.CUSTNAME, SOP30200.CSTPONBR, SOP30200.PRSTADCD, 
	SOP30200.ShipToName, SOP30200.ADDRESS1, SOP30200.ADDRESS2, SOP30200.ADDRESS3, 
	SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, SOP30200.COUNTRY, 
	SOP30200.TRDISAMT, SOP30200.ORTDISAM, SOP30200.SUBTOTAL, SOP30200.ORSUBTOT, 
	SOP30200.FRTAMNT, SOP30200.ORFRTAMT, SOP30200.TAXAMNT, SOP30200.ORTAXAMT, 
	SOP30200.DOCAMNT, SOP30200.ORDOCAMT, SOP30200.MSTRNUMB, CN00500.USERDEF1,
    	RM00102.ADDRESS1 BADDRESS1, RM00102.ADDRESS2 BADDRESS2, RM00102.ADDRESS3 BADDRESS3, RM00102.COUNTRY BCOUNTRY, 
	RM00102.CITY BCITY, RM00102.STATE BSTATE, RM00102.ZIP BZIP, 
	RBI10200.RBI_Cartons_Shipped_This, RBI10200.RBI_Total_Weight, 
	RBI10200.RBI_Shipment_Number, RBI10200.RBI_Ship_Carrier_Used, 
	RBI10200.RBI_Pickup_Number, RBI31200.RBI_Customer_XRef, RBI31100.RBI_VENDOR_NUMBER, 
	RBI31100.RBI_DEPARTMENT_NUMBER, RBI31100.RBI_STORE_NUMBER, SOP30300.LNITMSEQ, 
	SOP30300.CMPNTSEQ, SOP30300.ITEMNMBR, SOP30300.ITEMDESC, SOP30300.LOCNCODE, 
	SOP30300.UNITPRCE, SOP30300.ORUNTPRC, SOP30300.XTNDPRCE, SOP30300.OXTNDPRC, 
	SOP30300.QUANTITY, SOP30300.QTYORDER, SOP30300.QTYTBAOR, SOP30300.QTYTOINV, 
	SOP30300.QTYTORDR, SOP30300.COMMNTID, SOP30300.DECPLCUR, SOP30300.ODECPLCU,
	SOP30300.QTYREMAI, IV00101.USCATVLS_3,
    	IV00101.DECPLQTY, SOP10202.CMMTTEXT, vRBI10200Weight.total_weight,AIC_BackOrder_Flag,
	SOP10106.CMMTTEXT COMMENT,DMSCOMSN.SLPRSNID,ASILOC50.ASICUSTITNO,
	dbo.MinInvoice (SOP30200.mstrnumb) SOPMin,BackCancel.QTYCANCE
FROM
    { oj (((((((((((((ACME.dbo.SOP30200 SOP30200     
LEFT OUTER JOIN ACME.dbo.CN00500 CN00500     
	ON SOP30200.CUSTNMBR = CN00500.CUSTNMBR)
LEFT OUTER JOIN ACME.dbo.SOP30300 SOP30300     
	ON          SOP30200.SOPTYPE = SOP30300.SOPTYPE   
	AND      SOP30200.SOPNUMBE = SOP30300.SOPNUMBE)           
LEFT OUTER JOIN ACME.dbo.SOP10106 SOP10106     
	ON          SOP30200.SOPTYPE = SOP10106.SOPTYPE   
	AND      SOP30200.SOPNUMBE = SOP10106.SOPNUMBE)
LEFT OUTER JOIN ACME.dbo.RBI10200 RBI10200     
	ON          SOP30200.ORIGTYPE = RBI10200.SOPTYPE   
	AND      SOP30200.ORIGNUMB = RBI10200.SOPNUMBE   
	AND       SOP30300.LNITMSEQ = RBI10200.LNITMSEQ)
LEFT OUTER JOIN ACME.dbo.RBI31200 RBI31200     
	ON SOP30200.ORIGNUMB = RBI31200.SOPNUMBE   
	AND SOP30200.ORIGTYPE = RBI31200.SOPTYPE 
	AND SOP30300.LNITMSEQ = RBI31200.LNITMSEQ)          
LEFT OUTER JOIN ACME.dbo.RBI31100 RBI31100     
	ON          SOP30200.ORIGNUMB = RBI31100.SOPNUMBE   
	AND      SOP30200.ORIGTYPE = RBI31100.SOPTYPE)           
LEFT OUTER JOIN ACME.dbo.RM00102 RM00102     
	ON          SOP30200.CUSTNMBR = RM00102.CUSTNMBR   
	AND      SOP30200.PRBTADCD = RM00102.ADRSCODE)           
LEFT OUTER JOIN ACME.dbo.SOP10202 SOP10202     
	ON          SOP30300.SOPNUMBE = SOP10202.SOPNUMBE   
	AND      SOP30300.SOPTYPE = SOP10202.SOPTYPE   
	AND      SOP30300.CMPNTSEQ = SOP10202.CMPNTSEQ   
	AND      SOP30300.LNITMSEQ = SOP10202.LNITMSEQ)          
LEFT OUTER JOIN ACME.dbo.IV00101 IV00101     
	ON          SOP30300.ITEMNMBR = IV00101.ITEMNMBR)  
LEFT OUTER JOIN ACME.dbo.vRBI10200Weight vRBI10200Weight  
 	ON          RBI10200.SOPNUMBE = vRBI10200Weight.sopnumbe
   	AND     RBI10200.RBI_PIN = vRBI10200Weight.rbi_pin)
LEFT OUTER JOIN ACME.dbo.AIC00101 AIC00101  
 	ON          SOP30200.CUSTNMBR = AIC00101.CUSTNMBR)
LEFT OUTER JOIN ACME.dbo.DMSCOMSN DMSCOMSN  
 	ON  SOP30200.SOPNUMBE = DMSCOMSN.SOPNUMBE
	AND SOP30200.SOPTYPE = DMSCOMSN.SOPTYPE
	AND SOP30300.LNITMSEQ = DMSCOMSN.LNITMSEQ
	AND DMSCOMSN.SEQNUMBR = 1)
LEFT OUTER JOIN ACME.dbo.ASILOC50 ASILOC50  
 	ON  SOP30300.ITEMNMBR = ASILOC50.ITEMNMBR
	AND SOP30200.CUSTNMBR = ASILOC50.CUSTNMBR)
LEFT OUTER JOIN 
	(select SOPNUMBE,SOPTYPE,LNITMSEQ,QTYREMAI,QTYCANCE,QTYPRINV from SOP10200 where soptype = 2
	union all
	select SOPNUMBE,SOPTYPE,LNITMSEQ,QTYREMAI,QTYCANCE,QTYPRINV from SOP30300 where soptype = 2) BackCancel
	ON SOP30200.ORIGNUMB = BackCancel.SOPNUMBE   
	AND SOP30200.ORIGTYPE = BackCancel.SOPTYPE 
	AND SOP30300.LNITMSEQ = BackCancel.LNITMSEQ}    

WHERE SOP30200.SOPNUMBE = view_lot_invoice_history.DOCNUMBR 
	AND SOP30200.SOPTYPE = @DOCTYPE 
	
ORDER BY
    SOP30200.SOPTYPE ASC,
    SOP30200.SOPNUMBE ASC,
    SOP30200.LOCNCODE ASC,
    SOP30300.LNITMSEQ ASC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Thanks

 

[vongrunt]

ANSI JOIN syntax is self-contained, so you can place JOIN anywhere in FROM list. After that WHERE clause can be reduced to single condition (SOP30200.SOPTYPE = @DOCTYPE)

Btw. current sproc code is invalid (view_lot_invoice_history is nowhere declared).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)