Except operation in TSQL returning error

Question

I need to apply an EXCEPT (same as MINUS in Oracle) operation in TSQL. I tried with a simple code

select * from Table1
except
select * from Table1 where calndr_dt between '2014-10-01' and '2014-10-10'`

Each sub query is executing fine. But when joined with EXCEPT, it is returning the following error message.

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 2, column: 1: Incorrect syntax near 'except'.

I assume your actual query is more complex because surely this could be done by just running the second query and bracketing the `WHERE` clause with `NOT ()`? — Damien_The_Unbeliever, Oct 10 '14 at 07:37
It doesn't say "Incorrect syntax near *the keyword* 'except'."? What version are you on? If <2005 it is not available. — Martin Smith, Oct 10 '14 at 07:44
Yes the actual query is much more complex. There also I got the same error. That is why I thought of experimenting with a simple piece of code. — Thomas Cherian, Oct 10 '14 at 08:51
It works on SQLFiddle, so I'm not sure where the error could be? Could you post the whole code? http://sqlfiddle.com/#!6/dcf8f/1 — NickyvV, Oct 10 '14 at 09:18
You could have some incorrect line endings maybe? http://stackoverflow.com/questions/7738253/incorrect-syntax-near-go-in-sql-server-management-studio — NickyvV, Oct 10 '14 at 09:21
@NickyvV this is the actual code used for the experiment. select * from calndr_dimn except select * from calndr_dimn where calndr_dt between '2014-10-01' and '2014-10-10' — Thomas Cherian, Oct 10 '14 at 09:30
Microsoft SQL Server 2012 - 10.0.4830.1 (X64) Aug 27 2014 18:15:02 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 (Build 9200: ) — Thomas Cherian, Oct 13 '14 at 06:15

score 0 · Answer 1 · answered Oct 11 '14 at 18:00

0

Remove the ORDER BY clause from the first query, if you have this clause.

answered Oct 11 '14 at 18:00

Razvan Socol

1 Answers1