November 1, 2005 at 11:17 am
I have a transaction table and a code table that are joined as follows
TransData td LEFT OUTER JOIN Code c ON td.GLAcct = c.Code
I want to exclude certain GLAcct values, and I thought creating a table variable would be the fastest was to do it (rather than saying 'not( GLAcct in ('1', '2', '3')) in the Where clause), but I can't figure out the syntax for joining that table.
The DDL for the table variable is as follows:
DECLARE @ExcludedGLAccounts TABLE
( GLAcct VARCHAR(20) PRIMARY KEY CLUSTERED)
Insert Into @ExcludedGLAccounts Values ('1')
Insert Into @ExcludedGLAccounts Values ('2')
Insert Into @ExcludedGLAccounts Values ('3')
Questions:
1. Is the table variable the route to take?
2. Whether it is or not, what is the syntax to join the table variable to exclude its accounts?
Thanks,
Mattie
November 1, 2005 at 1:34 pm
TransData td
LEFT OUTER JOIN Code c ON td.GLAcct = c.Code
LEFT OUTER JOIN @ExcludedGLAccounts e ON e.GLAcct= td.GLAcct
WHERE e.GLAcct IS NULL
I'd benchmark one method against the other to determine which is more efficient. Your @table might benefit ( depending on the number of rows ) from having an index defined.
You might also consided making this exclusion list a permanent table, thus eliminating the need to modify the procedure when additions or deletions from the list are necessary.
Mike
November 1, 2005 at 1:37 pm
SET NOCOUNT ON
DECLARE @TransData TABLE
(
GLAcct VARCHAR(25),
AcctDetls VARCHAR(10)
)
INSERT @TransData
SELECT '1', 'AcctDetls1' UNION
SELECT '2', 'AcctDetls2' UNION
SELECT '3', 'AcctDetls3' UNION
SELECT '4', 'AcctDetls4' UNION
SELECT '5', 'AcctDetls5' UNION
SELECT '6', 'AcctDetls6' UNION
SELECT '7', 'AcctDetls7'
DECLARE @ExcludedGLAccounts TABLE
( GLAcct VARCHAR(20) PRIMARY KEY CLUSTERED)
Insert Into @ExcludedGLAccounts Values ('1')
Insert Into @ExcludedGLAccounts Values ('2')
Insert Into @ExcludedGLAccounts Values ('3')
SELECT td.*
FROM
@TransData td
LEFT JOIN
@ExcludedGLAccounts ea
ON
td.GLAcct = ea.GLAcct
WHERE
ea.GLAcct IS NULL
Damn. I am late.
Regards,
gova
November 1, 2005 at 2:34 pm
Thank you both so much. I couldn't get it out of my head that I should be using an exotic join condition or something other than 'equals'.
Mike, I appreciate your suggestion about the different methods, and a permanent table. The reason I want to use a table at all is that I have a 'union' statement in the stored procedure, and it uses the same 'where' clauses, and I just wanted to declare the excluded accounts in one spot.
And Govinn, it doesn't do me a bit of harm to read the same answer twice.
Thanks again.
Mattie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply