General information
Forum:
Microsoft SQL Server
Title:
Using local temporary tables in selects; good SQL books
I am writing a stored procedure that I need to break into 2 select statements because if I run it as one it exceeds the threshold on the server. I am doing the first select and sending it to a local table #shipper, but I can't get the 2nd select to use the #shipper table. Code below...
I am very new to SQL, I've taken the MS courses but I would like the names of some really good books on writing T-SQL and longer SP's.
Here's my code.
declare @lccustno char(4), @ldFrom datetime, @ldTo datetime
SET @lccustno = '0227'
SET @ldFrom = '01/01/2000'
SET @ldto = '08/31/2001'
SELECT OEP128.*, OEP128A.DSHIPPED AS DSHIPPED, OEP128A.SPFACL AS SPFACL, OEP128A.SPWBIL AS SPWBIL, OEP128A.SPSVIA AS SPSVIA,
MKP100A.CDESC AS CDESC, PPRODFL.TATPD AS TATPD, PPRODFL.PNLTYC AS PNLTYC,
PBKLOGAF.BKPONO AS BKPONO
into #shipper2
FROM OEP128 Left outer JOIN
OEP128A ON OEP128.CJOBORDER = OEP128A.CJOBORDER AND OEP128.ISHIPPERNO = OEP128A.ISHIPPERNO LEFT OUTER JOIN
PBKLOGAF ON OEP128.CJOBORDER = PBKLOGAF.BKORDN AND OEP128.ISHIPPERNO = PBKLOGAF.ISHIPPER LEFT OUTER JOIN
PPRODFL ON OEP128.CTIMECHARG = PPRODFL.CTIMECHARG AND OEP128.CCUSTNO = PPRODFL.CCUSTNO LEFT OUTER JOIN
MKP100A ON OEP128.CMARKETCOD = SUBSTRING(MKP100A.KEY11, 1, 4)
WHERE (OEP128.CCUSTNO = @lcCustNo) AND (OEP128A.DSHIPPED >= @ldFrom) and (OEP128A.DSHIPPED <= @ldTo)
ORDER BY OEP128.CJOBORDER, OEP128.ISHIPPERNO, OEP128.SPITEM
go
select #shipper2.*, oep180.slsamt from #shipper
left outer join oep180
on #shipper2.cjoborder = oep180.cjoboder and #shipper2.ishipperno = oep180.ishipperno and #shipper2.spitem = oep180.citemno
ORDER BY #shipper2.CJOBORDER, #shipper2.ISHIPPERNO, #shipper2.SPITEM
go
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only