--LET SAY I NEED THE DISTINCT LIST OF KEY VALUES FROM CONTRACT MASTER AND HISTORY
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--I WILL BE DOING DIFFERENT THINGS WITH THE DATA RETURNED
--RATHER THAN RUN THE ABOVE QUERY MULTIPLE TIMES
--I CAN USE A TABLE VARIABLE
DECLARE @CONTRACTS AS TABLE(
CUSTNMBR CHAR(15),
ADRSCODE CHAR(15),
Contract_Number CHAR(11),
WSCONTSQ INT
,UNIQUE(CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ) --THIS TRICKS SQL INTO GIVING ME AN INDEX
)
INSERT INTO @CONTRACTS
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--NOW I CAN USE MY TABLE VARIABLE PRETTY MUCH LIKE A NORMAL TABLE
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM @CONTRACTS
SELECT *
FROM dbo.SV00582 S
INNER JOIN @CONTRACTS C
ON S.CUSTNMBR=C.CUSTNMBR
AND S.ADRSCODE=C.ADRSCODE
AND S.Contract_Number=C.Contract_Number
AND S.WSCONTSQ=C.WSCONTSQ
Did this help you? If so, please leave a comment!
David Jedziniak maintains this blog and posts helpful tips and tricks on SQL Server and Dynamics GP development. The opinions expressed here are those of the author and do not represent the views, express or implied, of any past or present employer. The information here is provided without warranty of fitness for any particular purpose.
Subscribe to:
Post Comments (Atom)
SQL 2022 TSQL snapshot backups!
SQL 2022 now actually supports snapshot backups! More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...
-
I love these new ribbons in Office. I created a custom ribbon and moved it to the top of the tab list show it is the default tab that show...
-
If you are reading this, you are probably aware of the fact that you cannot deploy SSRS 2008 reports on an SSRS2005 server. SSRS reports ar...
-
Requirement: Trim, truncate, or otherwise modify the text from one SharePoint list field to make it appear in another. Solution: Make the...
No comments:
Post a Comment