6
SELECT SKU
FROM PartProductSupplemental
EXCEPT
SELECT SKU
FROM Inventory

Why do I get this error:

Incorrect Syntax near the Word Except

I check on line and syntax is syntactically correct:

SELECT ProductID 
FROM Production.WorkOrder
EXCEPT
SELECT ProductID 
FROM Production.Product
Toby Allen
  • 10,997
  • 11
  • 73
  • 124
bizness
  • 95
  • 1
  • 1
  • 4
  • Try putting it on one line - it might just be a missing whitespace. – Goblin Nov 02 '10 at 19:42
  • I know this is flagged correctly, but in the future using SQL 08 as a title could make people think you are talking about an ANSI standard when just reading the title vs a Microsoft product. More reasonable title would change SQL 08 would be Microsoft SQL Server 2008. Picky I know:). – Kuberchaun Nov 02 '10 at 19:44

3 Answers3

9

Your database compatibility mode is probably set to 2000 (80) or earlier.

In Management Studio:

  1. Right click on the database name under the "Databases" heading in the Object Explorer
  2. In the Properties window that pops up, select "Options" - Compatibility Level is third from the top, on the right.
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Vidar Nordnes
  • 1,334
  • 10
  • 20
0

try using distinct and MINUS just to test.

Except should have worked as well, are the fields of the same type ?

(it works also on 2005, according to documentation and you don't need () on the 2nd phrase).

Dani
  • 14,639
  • 11
  • 62
  • 110
0

When I run the following it works fine:

with PartProductSupplemental as
(
  SELECT 1 sku
  UNION
  select 2
  UNION
  SELECT 3
  UNION
  select 4
  UNION 
  SELECT 5
),
Inventory as
(
  SELECT 1 sku
  UNION
  select 2
  UNION
  SELECT 3

)

SELECT SKU
FROM PartProductSupplemental
EXCEPT
SELECT SKU
FROM Inventory

Are you sure this is actually what you are running? Is there any sql above that?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • No i swear it just says incorrect syntax near except, and im like but why, and then i look on interwebs and it says this is how we do it – bizness Nov 02 '10 at 19:40
  • Lol, i believe you that it throws the error. Is there any SQL above the section that uses the `EXCEPT`. Your syntax is valid, so something else is up. – Abe Miessler Nov 02 '10 at 19:42
  • SELECT COUNT (*) FROM(SELECT SKU FROM PartProductSupplemental EXCEPT SELECT SKU FROM Inventory ) – bizness Nov 02 '10 at 19:43
  • 1
    I tested on 2005 with a db set to compatibility 80 -- worked fine for me, no errors – OMG Ponies Nov 02 '10 at 19:54
  • I think the real problem has to do with his `SELECT COUNT FROM (SubQusery)` but oh well – Abe Miessler Nov 02 '10 at 20:45