Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EXCEPT usage Expand / Collapse
Author
Message
Posted Monday, October 25, 2010 7:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1010499
Posted Monday, October 25, 2010 8:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 4,363, Visits: 9,545
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1010509
Posted Monday, October 25, 2010 8:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1010510
Posted Tuesday, October 26, 2010 6:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1010734
Posted Tuesday, October 26, 2010 11:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1011021
Posted Wednesday, October 27, 2010 8:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #1011561
Posted Wednesday, October 27, 2010 5:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1011949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse