Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXCEPT usage


EXCEPT usage

Author
Message
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6345 Visits: 7660
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')

SELECT Key1, 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
ON psa.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?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5104 Visits: 9859
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6345 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
drew.allen
drew.allen
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3501 Visits: 10330
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6345 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Piotr.Rodak
Piotr.Rodak
SSC Eights!
SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)SSC Eights! (888 reputation)

Group: General Forum Members
Points: 888 Visits: 1761
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
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6345 Visits: 7660
Piotr.Rodak (10/27/2010)
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


Pretty much, but hadn't realized that the subquery, and thus a different execution plan, would be required.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search