October 25, 2010 at 7:19 pm
Alrighty, trying to wrap my head around some of the new T-SQL commands for 2k5+ and I've run headlong into EXCEPT. Which is just lovely, except for one thing. I can't seem to do what I want with it.
For a sample of my attempt:
CREATE TABLE #tmp
(Key1 VARCHAR(10),
Key2 VARCHAR(10)
)
CREATE TABLE #tmp2
(Key1 VARCHAR(10),
Key2 VARCHAR(10),
Key3 VARCHAR(10),
Key4 VARCHAR(10)
)
INSERT INTO #tmp VALUES('aa', 'bb')
INSERT INTO #tmp VALUES ('cc', 'dd')
INSERT INTO #tmp VALUES ('ee', 'ff')
INSERT INTO #tmp VALUES ('gg', 'hh')
INSERT INTO #tmp2 VALUES ( 'aa', 'zz', 'abc', 'def')
INSERT INTO #tmp2 VALUES ( 'bb', 'ee', 'abc', 'def')
INSERT INTO #tmp2 VALUES ( 'cc', 'gg', 'abc', 'def')
INSERT INTO #tmp2 VALUES ( 'dd', 'zz', 'abc', 'def')
SELECTKey1, Key2
from#tmp
EXCEPT
SELECT Key1 FROM #tmp2
Which returns the lovely error:
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Okay, the error message is simple enough, but I thought the point of EXCEPT was to reword the anti semi-join operation? How would you, with except, do something like the following (since I don't have MERGE in my lovely 2k5...) which is the second statement of an UpSert...
INSERT INTO PortSecAgg
(PortfolioID,
SecurityID,
AsOfDate,
IsShortPosition,
PortfolioCode,
Symbol,
Quantity,
SectypeCode,
Cusip,
UnitCost,
TotalCost,
Price,
MarketValue,
AccruedInterest,
UnrealizedGL,
IsSubAccount
)
SELECT
vpsa_s.PortfolioID,
vpsa_s.SecurityID,
vpsa_s.AsOfDate,
CASE WHEN vpsa_s.Quantity < 0. THEN 1 ELSE 0 END AS IsShortPosition,
vpsa_s.PortfolioCode,
vpsa_s.Symbol,
vpsa_s.Quantity ,
vpsa_s.SectypeCode,
vpsa_s.Cusip,
vpsa_s.UnitCost,
vpsa_s.TotalCost,
vpsa_s.Price,
vpsa_s.MarketValue,
vpsa_s.AccruedInterest,
vpsa_s.UnrealizedGL ,
CASE WHEN vpsa_s.IsSubAccount = 'Y' THEN 1 else 0 END AS IsSubAccount
FROM
vw_PortSecAgg_Staging AS vpsa_s
LEFT JOIN
PortSecAgg AS psa
ONpsa.PortfolioID = vpsa_s.PortfolioID
AND psa.SecurityID = vpsa_s.SecurityID
AND psa.AsOfDate = vpsa_s.AsOfDate
AND psa.IsShortPosition = vpsa_s.IsShortPosition
WHERE
psa.PortfolioID IS NULL
The only way I could see doing this was doing an EXCEPT in a subquery, then bringing the results of that query out as a limiter for direct joining for the view. That's even more passes then I'd need in this older method. What's the benefit if you're not looking for exact matches on exact sized tables?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2010 at 8:11 pm
You have a simple mistake in your test:
SELECT key1, key2 FROM #tmp
EXCEPT
SELECT key1, key2 FROM #tmp2
I didn't thoroughly go through your code, but if all you are trying to do is a UPDATE/INSERT, then you could do something like:
;WITH updates ({list of columns for the CTE here})
AS (
SELECT key1, key2 FROM #tmp
EXCEPT
SELECT key1, key2 FROM #tmp2
)
SELECT *
INTO #updates
FROM updates;
UPDATE alias
SET ...
FROM YourFinalTable t
INNER JOIN #updates u ON u.key1 = t.key1 ...
INSERT INTO YourFinalTable (...)
SELECT ...
FROM YourFinalTable t
LEFT JOIN #updates u ON u.key1 = t.key1 ...
WHERE u.key1 IS NULL;
If you only need the insert portion, then you don't need the #updates temp table and you could perform the INSERT directly with a join to your CTE.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 25, 2010 at 8:16 pm
Jeffrey Williams-493691 (10/25/2010)
You have a simple mistake in your test:
SELECT key1, key2 FROM #tmp
EXCEPT
SELECT key1, key2 FROM #tmp2
Sadly, that wasn't a mistake, that's actually what I'm trying to do, and the system doesn't like the idea. I only want to except on some of the key columns, but return all the results from the first set that aren't in the second.
I didn't thoroughly go through your code, but if all you are trying to do is a UPDATE/INSERT, then you could do something like: (snip code)
Yes, but I've found that using the cte like that actually decreases the performance of the result, since now it not only has to do the anti semi-join, but it has to re-integrate the results as well. Have you noticed different results? I admit to not having a breadth of experience with the syntax but a couple of 500k row tables I'm working against as a test subject.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 26, 2010 at 6:40 am
Craig Farrell (10/25/2010)
Okay, the error message is simple enough, but I thought the point of EXCEPT was to reword the anti semi-join operation?
The purpose of EXCEPT is to handle set subtraction. This parallels UNION handling set addition and INTERSECT handling set intersection. Because they are set operators, the require that both sets in the operation have the same shape (in order to satisfy first normal form). They are not equivalent to various joins, although you may be able to achieve the same results with either method. Specifically, EXCEPT is designed to implement set subtraction, not replace the anti semi-join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 26, 2010 at 11:04 am
drew.allen (10/26/2010)
Craig Farrell (10/25/2010)
Okay, the error message is simple enough, but I thought the point of EXCEPT was to reword the anti semi-join operation?The purpose of EXCEPT is to handle set subtraction. This parallels UNION handling set addition and INTERSECT handling set intersection. Because they are set operators, the require that both sets in the operation have the same shape (in order to satisfy first normal form). They are not equivalent to various joins, although you may be able to achieve the same results with either method. Specifically, EXCEPT is designed to implement set subtraction, not replace the anti semi-join.
Drew
Ah thank you Drew, that makes sense.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2010 at 8:07 am
Having said that, wouldn't you be looking for something like
select #tmp.key1, #tmp.key2 FROM #tmp
inner join
(
SELECT key1 FROM #tmp
EXCEPT
SELECT key1 FROM #tmp2
) q on #tmp.key1 = q.key1
?
Regards
Piotr
...and your only reply is slàinte mhath
October 27, 2010 at 5:10 pm
Piotr.Rodak (10/27/2010)
Having said that, wouldn't you be looking for something like
select #tmp.key1, #tmp.key2 FROM #tmpinner join
(
SELECT key1 FROM #tmp
EXCEPT
SELECT key1 FROM #tmp2
) q on #tmp.key1 = q.key1
?
Regards
Piotr
Pretty much, but hadn't realized that the subquery, and thus a different execution plan, would be required.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply